Technology Matters

Using Dates in MySQL

Add Days to a Date

mysql> SELECT ADDDATE('2008-01-02', 31);
        -> '2008-02-02'

Subtract Days Form a Date

SUBDATE(expr,days)

mysql> SELECT SUBDATE('2008-01-02 12:00:00', 31);
        -> '2007-12-02 12:00:00'

Formatting a Date

DATE_FORMAT(date,format)

Formats the date value according to the format string.

The following specifiers may be used in the format string. The ì%î character is required before format specifier characters.

Specifier 	Description
%a 	Abbreviated weekday name (Sun..Sat)
%b 	Abbreviated month name (Jan..Dec)
%c 	Month, numeric (0..12)
%D 	Day of the month with English suffix (0th, 1st, 2nd, 3rd, Ö)
%d 	Day of the month, numeric (00..31)
%e 	Day of the month, numeric (0..31)
%f 	Microseconds (000000..999999)
%H 	Hour (00..23)
%h 	Hour (01..12)
%I 	Hour (01..12)
%i 	Minutes, numeric (00..59)
%j 	Day of year (001..366)
%k 	Hour (0..23)
%l 	Hour (1..12)
%M 	Month name (January..December)
%m 	Month, numeric (00..12)
%p 	AM or PM
%r 	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S 	Seconds (00..59)
%s 	Seconds (00..59)
%T 	Time, 24-hour (hh:mm:ss)
%U 	Week (00..53), where Sunday is the first day of the week
%u 	Week (00..53), where Monday is the first day of the week
%V 	Week (01..53), where Sunday is the first day of the week; used with %X
%v 	Week (01..53), where Monday is the first day of the week; used with %x
%W 	Weekday name (Sunday..Saturday)
%w 	Day of the week (0=Sunday..6=Saturday)
%X 	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x 	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y 	Year, numeric, four digits
%y 	Year, numeric (two digits)
%% 	A literal ì%î character
%x 	x, for any ìxî not listed above

Ranges for the month and day specifiers begin with zero due to the fact that MySQL allows the storing of incomplete dates such as ‘2014-00-00’.

Advertisement