MySQL's Greatest Date and Time Functions

DAYNAME(date), MONTHNAME(date)

These return the day of the week for the specified date (ie Monday, Tuesday etc) and the month for the specified date (January, October etc) respectively.

DAYOFYEAR(date), DAYOFMONTH(date), DAYOFWEEK(date), WEEKDAY(date)

Return a numeric value indicating the numerical day of the week, month, year respectively. Note that DAYOFWEEK(date) uses the ODBC standard, where Sunday is 0, Monday is 1, etc, while WEEKDAY(date) has Monday as 0, Tuesday as 1, etc.

WEEK(date[,mode]), MONTH(date), YEAR(date)

These return the week (numeric), month (numeric without leading zero) and year of the given date respectively. The mode parameter of WEEK() allows you to specify whether you want the weeks to be numbered 0 to 53 or 1 to 53 and whether a week starts on a Sunday or Monday. For instance, a mode value of 0 returns the week as numbered from 0 to 53, and starting on a Sunday. A mode of 5 is also numbered 0 to 53, but with weeks starting on a Monday.

ADDDATE(date, days), SUBDATE(date, days)

Returns a new date which is the specified number of days after (for ADDDATE()) or before (for SUBDATE()) the specified date.

ADDTIME(date/time, time), SUBTIME(date/time, time)

Returns a new date or time (depending on what was specified).

DATE_ADD(date, INTERVAL quantity unit), DATE_SUB(date, INTERVAL quantity unit)

Adds or subtracts the specified interval to the specified date. There are many types of units supported, including:

o MICROSECOND
o SECOND
o HOUR
o DAY
o WEEK (MySQL v5 +, use 7 DAY for earlier versions)
o MONTH
o QUARTER (MySQL v5 +, use 3 MONTH for earlier versions)
o YEAR

Some can be combined, eg HOUR_MICROSECOND (quantity would be in the form HH.mm), DAY_SECOND (quantity in form DD HH:MM:SS), YEAR_MONTH (in the form YY-MM).

CONVERT_TZ(date/time, from_tz, to_tz)

Converts date/time from from_tz timezone to to_tz timezone.

DATEDIFF(date1, date2)

Returns how many days date1 is after date2, ie the difference in days between the two dates. For datetime values, the time component is ignored. If date2 is after date1, the result is negative, otherwise it is positive.

TIMEDIFF(datetime, datetime)

Like DATEDIFF, but returns the difference as a time in the format 'HH:MM:SS', with leading zeros. The arguments can be either datetime, or time, but both must be the same. The maximum difference it can handle (ie return) is +/-838:59:59.

DATE_FORMAT(date,format)

Formats the date value according to the format string. The format string is similar to the format string used by the PHP date() function. Unfortunately, the codes for different date elements are often different. For example, to get a date in the form 2nd Oct 1976 you would use DATE_FORMAT(date,'%D %b %Y'), quite different to the format string you'd use for the same result from PHP's date() function.

The most useful date format codes are:

o %a - first 3 letters of the day (eg Wed, Sat)
o %W - full day of the week (eg Wednesday, Saturday)
o %w - numerical day of the week (0=Sunday to 6=Saturday)
o %D - numerical day of the month with suffix (eg 1st, 12th)
o %d - numerical day of the month with leading zero (eg 01, 12)
o %e - numerical day of the month without leading zero (eg 1, 12)
o %j - numerical day of the year (001..366)
o %b - first 3 letters of the month (eg Jan, Oct)
o %M - full name of the month (eg January, October)
o %m - numerical month of the year with leading zero (eg 01, 10)
o %c - numerical month of the year without leading zero (eg 1, 10)
o %y - year as two digits (eg 56, 04)
o %u/%U - week of the year as two digits (lower case has Mon as first day, upper case counts Sun as first)
o %Y - year as four digits (eg 1956, 2004)

You can also use this function to get formatted times for time or datetime/timestamp columns:

o %T - full time in 24 hour format with leading zeros (eg 01:10:06, 15:07:28)
o %H - hour in 24 hour time with leading zero (eg 01, 23)
o %k - hour in 24 hour time without leading zero (eg 1, 23)
o %r - full time in 12 hour format with leading zeros and AM or PM (eg 01:10:06AM, 03:07:28PM)
o %I or %h - hour in 12 hour time with leading zero (eg 01, 11)
o %l (little L)- hour in 12 hour time without leading zero (eg 1, 11)
o %p - AM or PM
o %i - minutes with leading zero (eg 03, 55)
o %f - Microseconds (000000..999999)

STR_TO_DATE(string, format)

The compliment of DATE_FORMAT() is this function.

1 2 3