SQL DATE_FORMAT – SQL Tutorial

SQL DATE_FORMAT() is a MySQL function which returns date/time in your requested format.

DATE_FORMAT(date-expression, format)

date-expression can be any valid date/time, and the format can be any of the following:

Format	Note	Example
%a	Abbreviated weekday name	Sun, Mon, Tue, Wed, Thu, Fri, Sat
%b	Abbreviated month name	Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
%c	Month, numeric	1-12
%D	Day of month with English suffix	1st, 2nd, 3rd, 4th, …, 30th, 31st
%d	Day of month, numeric	00-31
%e	Day of month, numeric	0-31
%f	Microseconds
%H	Hour	00-23
%h	Hour	01-12
%I	Hour	01-12
%i	Minutes	00-59
%j	Day of year	001-366
%k	Hour	0-23
%l	Hour	1-12
%M	Month name	January, February, March, April, May, June, July, August, September, October, November, December
%m	Month	01-12
%p	AM or PM
%r	Time, 12-hour	01:03:03 AM, 03:30:03 PM
%S	Seconds	00-59
%s	Seconds	00-59
%T	Time, 24-hour	15:30:46
%U	Week, where Sunday is the first day of week	00, 01, 02, …, 52
%u	Week, where Monday is the first day of week	00, 01, 02, …, 52
%V	Week, where Sunday is the first day of week, used with %X	01, 02, …, 53
%v	Week, where Monday is the first day of week, used with %x	01, 02, …, 53
%W	Weekday name	Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
%w	Day of the week (0=Sunday, 6=Saturday)	0, 1, 2, 3, 4, 5, 6
%X	Year of the week where Sunday is the first day of week, four digits, used with %V	2009, 2010, 2011, 2012
%x	Year of the week where Monday is the first day of week, four digits, used with %v	2009, 2010, 2011, 2012
%Y	Year, four digits	2009, 2010, 2011, 2012
%y	Year, two digits	09, 10, 11, 12
%%	The sign %	%

SELECT DATE_FORMAT('2011-09-05 13:00:00','%Y/%m/%d %T')

The result will look like:
2011/09/05 13:00:00

SELECT DATE_FORMAT(NOW(),'%b %e, %Y')

The result will look like:
Oct 2, 2011

SELECT DATE_FORMAT(NOW(),'%b %D, %Y %r')

The result will look like:
Oct 2nd, 2011 01:17:06 AM