DATE_FORMAT

Syntax

DATE_FORMAT (dateobj, format)

Arguments

dateobj: a valid date, datetime, or parsable date string
format: a string containing format symbols

Year arguments

Symbol	Output
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%j	Day of year (001 to 366)

Month arguments

Symbol	Output
%b	Abbreviated month (Jan, Feb, Mar…)
%M	Month name (January to December)
%m	Month number, padded (00 to 12)
%c	Month number (0 to 12)

Day arguments

Symbol	Output
%D	Day of the month with suffix (0th, 1st, 2nd, etc)
%d	Day of the month, padded (00 to 31)
%e	Day of the month (0 to 31)

Hour arguments

Name	Description
%H	Hour of day, padded 24h format (00 to 23)
%h	Hour of day, padded 12h format (01 to 12)
%I	(alias for %h)
%k	Hour of day, 24h format (0 to 23)
%l	Hour of day, 12h format (1 to 12)

Minute arguments

Symbol	Output
%i	Minute of hour (00 to 59)

Second arguments

Symbol	Output
%S	Seconds (00 to 59)
%s	Seconds (00 to 59)

Microsecond arguments

Symbol	Output
%f	Microseconds (000000 to 999999)

Time arguments

Symbol	Output
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%T	Time, 24-hour (hh:mm:ss)

Week arguments

Symbol	Output
%U	Week (00 to 53) starting Sunday
%u	Week (00 to 53) starting Monday
%V	Week (01 to 53) starting Sunday
%v	Week (01 to 53) starting Monday
%X	Year corresponding to week given by %V
%x	Year corresponding to week given by %v

Weekday arguments

Symbol	Output
%W	Weekday name (Sunday to Saturday)
%w	Day of the week (0=Sunday to 6=Saturday)
%a	Abbreviated day of week (Sun, Mon, Tue, etc)

Escape arguments

Symbol	Output
%%	A literal % character

Return Type

String

Examples

SQL

SELECT DATE_FORMAT(NOW(), '%M %D, %Y');

+---------------------------------+
| DATE_FORMAT(NOW(), '%M %D, %Y') |
+---------------------------------+
| March 3rd, 2015                 |
+---------------------------------+

SQL

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s');

+-----------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s') |
+-----------------------------------------+
| 2015-03-03 11:37:07                     |
+-----------------------------------------+

SQL

SELECT DATE_FORMAT(NOW(), '%W, the %D day of %M in that remarkable year %Y') AS florid;

+------------------------------------------------------------+
| florid                                                     |
+------------------------------------------------------------+
| Tuesday, the 3rd day of March in that remarkable year 2015 |
+------------------------------------------------------------+

Last modified: May 31, 2023