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

Last modified: May 31, 2023

Was this article helpful?