MySQL Date & Time functions

There is exclusive fields type to store Date and Date –Time in mysql or in other databases. As we know in any script date plays important role and we need to do many calculation and formatting to handle the date issues. It can be a simple Library book issue management to a customer compliant handling, these date fields are important for handling related data in a table.

MySQL have several date & time related functions to use in different situations and requirements. We will discuss some of the important functions here and use them for developing some sample codes.

You can read php date & time functions.
CURDATE()Current Date using MYSQL Query
DATE_ADD()Add by specifying INTERVAL in days, month, year , Hour, minutes etc
DATE_SUB()Subtracting Day, month , year , time etc
BETWEENRecords Between two dates
STR_TO_DATE()Convert String to Date & time Format as required by MySQL date field
TO_DAYS()Difference in days between two Dates
DATE_FORMAT()Formatting Date & Time output as required
DAY()Day part of the date field
DAYNAME()Day Name in Full ( Monday )
DAYOFWEEK()Weekdays starting from Sunday as 1 and ending with Saturday as 7
DAYOFYEAR()Number of the day in a Year ( 1 to 366 )
LAST_DAY()Last Day of the month
MONTH()Month part of the date field
MAKEDATE()Date from Year and DayOfYear
MONTHNAME()Full Month name from date
NOW()Current Date and Time
YEAR()Year part of the date field
YEARWEEK()Year with Week number of the year
WEEK()Week number in a year (0 - 53)
WEEKDAY()Day Week number in a week (0 - 6)
WEEKOFYEAR()Week number in a year (1 - 53)

From Date of Birth Calculate the retirement date

The person will retire on the last working day of the retirement month.

Group by in date field to count records of each day

Getting records of each day by using group by and count command in mysql date field

Format to use for creating date string before storing in Date field.

Date fields of MySQL table accept a particular format of Date & time strings, so we have to create such an acceptable format while storing Date values.

Collecting date stored as string in varchar field.

We can use str_to_date function to format date value stored as string in a varchar field ( not date field )

Adding present date & time automatically to mysql table

We can store the present date & time in a DateTime field while updating adding a record by using default value to CURRENT_TIMESTAMP

Formatting returned date value using strtotime function

We can format the returned date value from table by using strtotime function.
Your Rating

deepak verma


a really good work. one of the best sites that gives u exact working answers
noraina norddin


how to insert current time in mysql for automatic using php??


really good

Post Comment This is for short comments only. Use the forum for more discussions.

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2016 All rights reserved worldwide Privacy Policy Disclaimer