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
memsql> select date_format(now(), '%M %D, %Y');
+---------------------------------+
| date_format(now(), '%M %D, %Y') |
+---------------------------------+
| March 3rd, 2015 |
+---------------------------------+
memsql> 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 |
+-----------------------------------------+
memsql> 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 |
+------------------------------------------------------------+