SQL Date Functions

Name	Description
ADDDATE()	Adds dates: SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY); -- 1998-02-02
ADDTIME()	Adds time: SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002'); -- 1998-01-02 01:01:01.000001
CONVERT_TZ()	Converts from one timezone to another: SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00'); -- 2004-01-01 22:00:00
CURDATE(), CURRENT_DATE(), CURRENT_DATE	Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context: SELECT CURDATE(); -- 1997-12-15 SELECT CURDATE() + 0; -- 19971215
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	Synonyms for NOW()
CURTIME(), CURRENT_TIME(), CURRENT_TIME	Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone: SELECT CURTIME(); -- 23:50:26 SELECT CURTIME() + 0; -- 235026
DATE_ADD()	This function perform date arithmetic. Date is a DATETIME or DATE value specifying the starting date. Expr is an expression specifying the interval value to be added or subtracted from the starting date. Expr is a string; it may start with a ‘-‘ for negative intervals. Unit is a keyword indicating the units in which the expression should be interpreted. SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); -- 1998-01-01 00:01:00
DATE_FORMAT()	Formats date as specified: SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); -- Saturday October 1997
DATE_SUB(), SUBDATE()	Similar to DATE_ADD()
DATE()	Extracts the date part of a date or datetime expression: SELECT DATE('2003-12-31 01:02:03'); -- 2003-12-31
DATEDIFF()	DATEDIFF() returns expr1 . expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation: SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30'); -- 1
DAYNAME()	Returns the name of the weekday: SELECT DAYNAME('1998-02-05'); -- Thursday
DAY() DAYOFMONTH()	Returns the day of the month (1-31): SELECT DAYOFMONTH('1998-02-03'); -- 3
DAYOFWEEK()	Returns the weekday index for date (1 = Sunday, 2 = Monday, ., 7 = Saturday). These index values correspond to the ODBC standard. SELECT DAYOFWEEK('1998-02-03'); -- 3
DAYOFYEAR()	Returns the day of the year (1-366): SELECT DAYOFYEAR('1998-02-03'); -- 34
EXTRACT	The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic. SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03'); -- 199907
FROM_DAYS()	Converts a day number to a date: SELECT FROM_DAYS(729669); -- 1997-10-07
FROM_UNIXTIME()	Formats date as a UNIX timestamp
HOUR()	Extracts the hour: SELECT HOUR('10:05:03'); -- 10
LAST_DAY	Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid: SELECT LAST_DAY('2003-02-05'); -- 2003-02-28
MAKEDATE()	Returns a date, given year and day-of-year values. Dayofyear must be greater than 0 or the result is NULL: SELECT MAKEDATE(2001,31), MAKEDATE(2001,32); '2001-01-31', '2001-02-01'
MAKETIME, MAKETIME()	Returns a time value calculated from the hour, minute and second arguments: SELECT MAKETIME(12,15,30); -- '12:15:30'
MICROSECOND()	Returns the microseconds from argument
MINUTE()	Returns the minute from the argument
MONTH()	Returns the month from the date passed
MONTHNAME()	Returns the name of the month: SELECT MONTHNAME('1998-02-05'); -- February
NOW(), LOCALTIME(), LOCALTIME, LOCALTIMESTAMP, LOCALTIMESTAMP()	Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone: SELECT NOW(); -- 1997-12-15 23:50:26
PERIOD_ADD()	Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value: SELECT PERIOD_ADD(9801,2); -- 199803
PERIOD_DIFF()	Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values: SELECT PERIOD_DIFF(9802,199703); -- 11
QUARTER()	Returns the quarter from a date argument: SELECT QUARTER('98-04-01'); -- 2
SEC_TO_TIME()	Returns the seconds argument, converted to hours, minutes and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context: SELECT SEC_TO_TIME(2378); -- 00:39:38
SECOND()	Returns the second (0-59)
STR_TO_DATE()	This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts or a DATE or TIME value if the string contains only date or time parts: SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y'); -- 2004-04-31
SUBTIME()	Returns expr1 . expr2 expressed as a value in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time: SELECT SUBTIME('1997-12-31 23:59:59.999999', '1 1:1:1.000002'); -- 1997-12-30 22:58:58.999997
SYSDATE()	Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context: SELECT SYSDATE(); -- 2006-04-12 13:47:44
TIME_FORMAT()	This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes and seconds. If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12. SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l'); -- 100 100 04 04 4
TIME_TO_SEC()	Returns the argument converted to seconds
TIME()	Extracts the time part of the time or datetime expression expr and returns it as a string: SELECT TIME('2003-12-31 01:02:03'); -- 01:02:03
TIMEDIFF()	Returns expr1 . expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type: SELECT TIMEDIFF('1997-12-31 23:59:59.000001', '1997-12-30 01:01:01.000002'); -- 46:58:57.999999
TIMESTAMP()	With a single argument, this function returns the date or datetime expression. With two arguments, the sum of the arguments. SELECT TIMESTAMP('2003-12-31'); -- 2003-12-31 00:00:00
TIMESTAMPADD()	Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR. The unit value may be specified using one of keywords as shown or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are legal. SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02'); -- 2003-01-02 00:01:00
TIMESTAMPDIFF()	Returns the integer difference between the date or datetime expressions datetime_expr1 and datetime_expr2. The unit for the result is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function. SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); -- 3
TO_DAYS()	Given a date, returns a day number (the number of days since year 0).
UNIX_TIMESTAMP()	Returns a UNIX timestamp
UTC_DATE()	Returns the current UTC date
UTC_TIME()	Returns the current UTC time
UTC_TIMESTAMP()	Returns the current UTC date and time
WEEK()	This function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used.
WEEKDAY()	Returns the weekday index for date (0 = Monday, 1 = Tuesday, . 6 = Sunday)
WEEKOFYEAR()	Returns the calendar week of the date as a number in the range from 1 to 53. WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date,3)
YEAR()	Returns the year
YEARWEEK()	Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year. SELECT YEARWEEK('1987-01-01'); -- 198653