|
Date and Time Functions :
This function is used to manipulate the display format of a date and time. Lets see some basic functions for date and time.
CURDATE() :
This date function returns the current date in the format 'YYYY-MM-DD' or 'YYYYMMDD'.
mysql> select curdate();
--> 2007-01-03
CURTIME() :
Returns the current time in the format 'HH:MM:SS' or 'HHMMSS'.
mysql> select curtime();
--> 17:33:07
DATEDIFF(expression1,expression2) :
expression1 and expression2 are date or date-and-time expressions. This function returns expression1 – expression2 expressed as a value in days from one date to the other. Here only the date parts will be considered for calculation.
mysql> select datediff('2007-2-6 17:33:25','2007-1-1');
--> 36
DATE_ADD(datetime, INTERVAL expression datetimetype) :
This date function adds the expression to the datetime supplied.
mysql> select date_add('2007-1-14', interval 15 day);
--> 2007-01-29
This function is same for DATE_SUB, but subtracting will take place instead of adding.
DAYNAME(date) :
Returns the name of the day for the specified date.
mysql> select dayname('2007-01-04');
--> Thursday
DAYOFMONTH(date) or DAY(date) :
Returns the date for the day of the month in the range of 1 to 31. DAY() is a synonym for DAYOFMONTH().
mysql> select dayofmonth('2007-01-04');
--> 4
DAYOFWEEK(date) :
Returns the day of the week in the numeric format as 1 for Sunday to 7 for Saturday.
mysql> select dayofweek('2007-01-04');
--> 5
DAYOFYEAR(date) :
Returns the day of the year for given date in the numeric format, in the range 1 to 366.
mysql> select dayofyear('2007-07-09');
--> 190
HOUR(time) :
Returns the hour of the specified time in the numeric format from 0 to 23.
mysql> select hour('14:46:12');
--> 14
MINUTE(time) :
Returns the minute of the specified time in the numeric format from 0 to 59.
mysql> select minute('14:46:12');
--> 46
MONTH(date) :
Returns the month for the given date in the numeric format, in the range 0 to 12.
mysql> select month('2007-07-09');
--> 7
MONTHNAME(date) :
Returns the name of the month for the specified date.
mysql> select monthname('2007-07-09');
--> July
NOW() :
This date time function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.
mysql> select now();
--> 2007-01-04 14:56:15
| 
