Declaration
DATE_FORMAT(date, format)
Description
Formats the date and time in the specified format. date specifies the date and time, and format specifies the output format.
The following table lists all output formats:
Format Specifier |
Meaning |
Format |
|---|---|---|
%a |
Abbreviation for week. | Sun..Sat |
%b |
English abbreviation of the month. | Jan..Dec |
%c |
The numeric form of the month. | 1..12 |
%D |
The English abbreviation for day. | 1st..31st |
%d |
The numeric form of the day. | 01..31 |
%e |
The numeric form of the day. | 1.. 31 |
%f |
Microseconds. | 000000..999999 |
%H |
Hour. | 00..23 |
%h |
Hour. | 01..12 |
%I |
Hour. | 01..12 |
%i |
minutes. | 00..59 |
%j |
The day of the year. | 001..366 |
%k |
Hour. | 0..23 |
%l |
Hour. | 0..12 |
%M |
The English name of the month. | January..December |
%m |
The numeric form of the month. | 01..12 |
%p |
Morning or afternoon. | AM/PM |
%r |
12-hour time format. | hh:mm:ss AM/PM |
%S |
Seconds. | 00..59 |
%s |
Seconds. | 00..59 |
%T |
24-hour time. | hh:mm:ss |
%U |
The week of the year, where Sunday is considered the first day of the week. | 00..53 |
%u |
The week of the year, where Monday is considered the first day of the week. | 00..53 |
%V |
The week of the year, where Sunday is considered the first day of the week (and %X(both used simultaneously). |
01..53 |
%v |
The week of the year, where Monday is considered the first day of the week (and %x(both used simultaneously). |
01..53 |
%W |
The English name of the week. | Sunday..Saturday |
%w |
The day of the week. | 0=Sunday..6=Saturday |
%X |
The year to which a week belongs. Sunday is considered the first day of the week (and %V(both used simultaneously). |
|
%x |
The year to which the week belongs. Monday is considered the first day of the week (and %v(Used together). |
|
%Y |
Four-digit year. | |
%y |
Two-digit year. | |
%% |
A literal character. Output a %. |
Examples
obclient> SELECT
DATE_FORMAT('2014-01-01', '%Y-%M-%d'),
DATE_FORMAT('2014-01-01', '%X-%V'),
DATE_FORMAT('2014-01-01', '%U')
\G
*************************** 1. row ***************************
DATE_FORMAT('2014-01-01', '%Y-%M-%d'): 2014-January-01
DATE_FORMAT('2014-01-01', '%X-%V'): 2013-52
DATE_FORMAT('2014-01-01', '%U'): 00
1 row in set