MySQL date format cheat sheet

Last updated on September 16th, 2020 at 10:47 am

MySQL Date format is a very handy function. It formats a date as specified in the argument. A list of format specifiers given bellow can be used to format a date. The ‘%’ is required before the format specifier characters. Here’s a cheat sheet for MySQL date format function.

MySQL date format syntax

DATE_FORMAT(date,format)
date – date string in the format ‘YYYY-MM-DD HH:MM:SS’. It can be a date column from a table, string as shown above or system function like NOW()

format – string in the format ‘%x’. x is an alphabet for the format string.

MySQL date format cheat sheet –

Format string	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%ac	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

Example:

#using system function now()
SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');

#using a string
SELECT DATE_FORMAT('2011-11-05 11:45:00','%b %d %Y %h:%i %p');

#using a date column
CREATE TABLE TEST(DT DATETIME);
INSERT INTO TEST(DT) VALUES('2010-10-05 10:45:00');
SELECT DATE_FORMAT(DT,'%b %d %Y %h:%i %p') from TEST;

Result:

Dec 04 2013 10:40 PM
Nov 05 2013 11:45 PM
Oct 05 2010 10:45 PM

As you can see, you can use spaces, comma or even hyphens in format string. MySQL date format function will retain them in the result. Rest of the string is replaced with appropriate formats. Here are some commonly used date formats that you can use.

#using system function now()
SELECT DATE_FORMAT(NOW(),'%M %d, %Y');
SELECT DATE_FORMAT(NOW(),'%b %d, %Y');
SELECT DATE_FORMAT(NOW(),'%c-%d-%Y');
SELECT DATE_FORMAT(NOW(),'%c-%d-%y');
SELECT DATE_FORMAT(NOW(),'%d/%c/%Y');
SELECT DATE_FORMAT(NOW(),'%d/%c/%y');
SELECT DATE_FORMAT(NOW(),'%b %d, %Y %h:%i %p');
SELECT DATE_FORMAT(NOW(),'%M %d, %Y %h:%i %p');

Their results

December 25, 2013
Dec 25, 2013
12-25-2013
12-25-2013
25/12/2013
25/12/13
Dec 25, 2013 10:40 PM
December 25, 2013 10:40 PM