Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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