MySQL – How to Format Date in MySQL with DATE_FORMAT()

MySQL supports formatting the datetime values into a different formats using DATE_FORMAT() function. This function accepts date/datetime values as a first parameter and returns into a specific format defined as a second parameter.

Let us explore this with the following examples which are self-explanatory

Define a DATETIME variable
SET @date:='2014-06-16 14:12:49';

-- Display datetime values in YYYY-mm-dd format
SELECT date_format(@date,'%Y-%m-%d') AS formatted_date;

The result is 2014-06-16

-- Display datetime values as Long Date format
SELECT date_format(@date,'%W, %M %d,%Y') AS formatted_date;

The result is Monday, June 16,2014

-- Display datetime values as Full date format
SELECT date_format(@date,'%W, %M %d,%Y %T') AS formatted_date;

The result is Monday, June 16,2014 14:12:49

-- Display datetime values in HH:MM:SS format
SELECT date_format(@date,'%T') AS formatted_date;

The result is 14:12:49

-- Display datetime values in Month Year format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is June 2014

-- Display datetime values in mm-dd-yyyy format
SELECT date_format(@date,'%m-%d-%Y')  AS formatted_date;

The result is 06-16-2014

-- Display datetime values in dd-mm-yyyy format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is 16-06-2014

The DATE_FORMAT Specifiers I Use the Most

Here is a short cheat sheet for DATE_FORMAT. %Y is the four-digit year and %y is the two-digit year. %m is the month as 01 to 12, %M is the full month name and %b is the short month name. %d is the day of the month as 01 to 31 and %e is the same without the leading zero. %H is the hour from 00 to 23, %h is the hour from 01 to 12 and %p adds AM or PM. %W is the weekday name and %a is its short form. %T is a shortcut for %H:%i:%s.

Watch out for one common trap. In MySQL, minutes are %i, not %M. %M gives you the month name, so a format like ‘%H:%M’ returns 14:June instead of 14:12. Also note that the last example in this post needs ‘%d-%m-%Y’ to return 16-06-2014. Month and day names follow the lc_time_names system variable, so you can get them in another language by changing it for your session. When you need to go the other way, from a formatted string back to a date, use STR_TO_DATE with the same specifiers.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.