Outdated Version

You are viewing an older version of this section. View current production version.

DATE_FORMAT

Convert the input datetime object to a string according to the specified 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

select date_format(now(), '%M %D, %Y');
+---------------------------------+
| date_format(now(), '%M %D, %Y') |
+---------------------------------+
| March 3rd, 2015                 |
+---------------------------------+

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

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