date_format
date_format(timestamp, fmt)
Converts a date into a string in the format specified by fmt.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT date_format('2009-10-04 22:23:00', '%M %D %W'); date_format -------------------- October 4th Sunday (1 row) SELECT date_format('2021-02-20 08:30:45', '%Y-%m-%d %H:%i:%S'); date_format --------------------- 2021-02-20 08:30:45 (1 row) SELECT date_format('2021-02-20 18:10:15', '%r-%T'); date_format ---------------------- 06:10:15 PM-18:10:15 (1 row) |
Table 1 describes the patterns of date parameter values. They can be used for the date_format, time_format, str_to_date, str_to_time, and from_unixtime functions.
Format | Description | Value |
|---|---|---|
%a | Abbreviated week name | Sun...Sat |
%b | Abbreviated month name | Jan...Dec |
%c | Month | 0...12 |
%D | Date with a suffix | 0th, 1st, 2nd, 3rd, ... |
%d | Day in a month (two digits) | 00...31 |
%e | Day in a month | 0...31 |
%f | Microsecond | 000000...999999 |
%H | Hour, in 24-hour format | 00...23 |
%h | Hour, in 12-hour format | 01...12 |
%I | Hour, in 12-hour format, same as %h | 01...12 |
%i | Minute | 00...59 |
%j | Day in a year | 001...366 |
%k | Hour, in 24-hour format, same as %H | 0...23 |
%l | Hour, in 12-hour format, same as %h | 1...12 |
%M | Month name | January...December |
%m | Month (two digits) | 00...12 |
%p | Morning and afternoon | AM PM |
%r | Time, in 12-hour format | hh::mm::ss AM/PM |
%S | Second | 00...59 |
%s | Second, same as %S | 00...59 |
%T | Time, in 24-hour format | hh::mm::ss |
%U | Week (Sunday is the first day of a week.) | 00...53 |
%u | Week (Monday is the first day of a week.) | 00...53 |
%V | Week (Sunday is the first day of a week). It is used together with %X. | 01...53 |
%v | Week (Monday is the first day of a week). It is used together with %x. | 01...53 |
%W | Week name | Sunday...Saturday |
%w | Day of a week. The value is 0 for Sunday. | 0...6 |
%X | Year (four digits). It is used together with %V. Sunday is the first day of a week. | - |
%x | Year (four digits). It is used together with %v. Monday is the first day of a week. | - |
%Y | Year (four digits) | - |
%y | Year (two digits) | - |
%% | Character '%' | Character '%' |
%x | 'x': any character apart from the preceding ones | Character 'x' |
In the preceding table, %U, %u, %V, %v, %X, and %x are not supported currently.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.
