SQL DATE_FORMAT

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

SQL DATE_FORMAT Syntax

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 % %

SQL DATE_FORMAT Example

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>