Syntax
DATE_FORMAT(date, format)
Purpose
DATE_FORMAT() exports the date and time in the specified format. The date parameter specifies the date and time, and the format parameter specifies the output format.
The following table lists all output formats.
| Specifier | Description | Format |
|---|---|---|
%a |
The abbreviation of the week. | Sun..Sat |
%b |
The abbreviation of the month. | Jan..Dec |
%c |
The numeric format of the month. | 1..12 |
%D |
The abbreviation of the day. | 1st..31st |
%d |
The numeric format of the day. | 01..31 |
%e |
The numeric format of the day. | 1.. 31 |
%f |
The value for microsecond. | 000000..999999 |
%H |
The value for hour. | 00..23 |
%h |
The value for hour. | 01..12 |
%I |
The value for hour. | 01..12 |
%i |
The value for minute. | 00..59 |
%j |
The day of the year. | 001..366 |
%k |
The value for hour. | 0..23 |
%l |
The value for hour. | 0..12 |
%M |
The English name of the month. | January..December |
%m |
The numeric format of the month. | 01..12 |
%p |
The morning or afternoon. | AM/PM |
%r |
The time in the 12-hour format. | hh:mm:ss AM/PM |
%S |
The value for second. | 00..59 |
%s |
The value for second. | 00..59 |
%T |
The time in the 24-hour format. | hh:mm:ss |
%U |
The week of the year, with Sunday taken as the first day of a week. | 00..53 |
%u |
The week of the year, with Monday taken as the first day of a week. | 00..53 |
%V |
The week of the year. The week starts on Sunday. %V is used together with %X. |
01..53 |
%v |
The week of the year. The week starts on Monday. %v is used together with %x. |
01..53 |
%W |
The English name of the week. | Sunday..Saturday |
%w |
The day of the week. | 0=Sunday..6=Saturday |
%X |
The week of the year. The week starts on Sunday. %X is used together with %V. |
|
%x |
The week of the year. The week starts on Monday. %x is used together with %v. |
|
%Y |
The year in four-digit format. | |
%y |
The year in two-digit format. | |
%% |
The literal % character. |
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