Syntax
DATE_FORMAT(date, format)
Purpose
You can call this function to export 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 supported in OceanBase Database.
| 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. The week starts from Sunday. | 00..53 |
%u |
The week of the year. The week starts from Monday. | 00..53 |
%V |
The week of the year. The week starts from Sunday. %V is used together with %X. |
01..53 |
%v |
The week of the year. The week starts from 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 from Sunday. %X is used together with %V. |
|
%x |
The week of the year. The week starts from Monday. %x is used together with %v. |
|
%Y |
The year in the four-digit format. | |
%y |
The year in the 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 (0.01 sec)