MySQL date format cheat sheet

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 stringDescription
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%acMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week
%uWeek (00..53), where Monday is the first day of the week
%VWeek (01..53), where Sunday is the first day of the week; used with %X
%vWeek (01..53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, 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
mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!