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 –

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

• About Author

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!