DATE_FORMAT
Declaration
DATE_FORMAT(date, format)
Description
Outputs 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 the output formats:
| Format | Description | Format |
|---|---|---|
%a | The English abbreviation of the day of the week. | Sun..Sat |
%b | The English abbreviation of the month. | Jan..Dec |
%c | The numeric representation of the month. | 1..12 |
%D | The English abbreviation of the day. | 1st..31st |
%d | The numeric representation of the day. | 01..31 |
%e | The numeric representation of the day. | 1.. 31 |
%f | Microseconds. | 000000..999999 |
%H | Hours. | 00..23 |
%h | Hours. | 01..12 |
%I | Hours. | 01..12 |
%i | Minutes. | 00..59 |
%j | The day of the year. | 001..366 |
%k | Hours. | 0..23 |
%l | Hours. | 0..12 |
%M | The English name of the month. | January..December |
%m | The numeric representation of the month. | 01..12 |
%p | AM or PM. | AM/PM |
%r | 12-hour clock time. | hh:mm:ss AM/PM |
%S | Seconds. | 00..59 |
%s | Seconds. | 00..59 |
%T | 24-hour clock time. | hh:mm:ss |
%U | The week number in the year, with Sunday as the first day of the week. | 00..53 |
%u | The week number in the year, with Monday as the first day of the week. | 00..53 |
%V | The week number in the year, with Sunday as the first day of the week (used with %X). | 01..53 |
%v | The week number in the year, with Monday as the first day of the week (used with %x). | 01..53 |
%W | The English name of the day. | Sunday..Saturday |
%w | The day of the week. | 0=Sunday..6=Saturday |
%X | The year of the week, with Sunday as the first day of the week (used with %V). | |
%x | The year of the week, with Monday as the first day of the week (used with %v). | |
%Y | The four-digit year. | |
%y | The two-digit year. | |
%% | A literal character, outputs a single %. |
Examples
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 (0.001 sec)