MySQL | DATE_FORMAT method
Start your free 7-days trial now!
MySQL's DATE_FORMAT(~) method returns a date or datetime formatted according to the provided format.
Parameters
1. date | date/datetime
The date or datetime to format.
2. format | string
The format to use for formatting.
Refer to 'List of formatting types' at the bottom of page.
Return value
The formatted date.
Examples
To return date with weekday name, day of month with English suffix, month name and 4 digit year:
SELECT DATE_FORMAT('2020-04-26 21:53:00', '%W %D %M %Y');
+---------------------------------------------------+| DATE_FORMAT('2020-04-26 21:53:00', '%W %D %M %Y') |+---------------------------------------------------+| Sunday 26th April 2020 |+---------------------------------------------------+
To return date with abbreviated weekday name, day of month, abbreviated month name and 2 digit year:
SELECT DATE_FORMAT('2020-04-26 21:53:00','%a %d %b %y');
+--------------------------------------------------+| DATE_FORMAT('2020-04-26 21:53:00','%a %d %b %y') |+--------------------------------------------------+| Sun 26 Apr 20 |+--------------------------------------------------+
List of formatting types
Below is a table with a list of valid formatting types:
Specifier | Description |
|---|---|
| Abbreviated weekday name (Sun..Sat) |
| Abbreviated month name (Jan..Dec) |
| Month, numeric (0..12) |
| Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
| Day of the month, numeric (00..31) |
| Day of the month, numeric (0..31) |
| Microseconds (000000..999999) |
| Hour (00..23) |
| Hour (01..12) |
| Hour (01..12) |
| Minutes, numeric (00..59) |
| Day of year (001..366) |
| Hour (0..23) |
| Hour (1..12) |
| Month name (January..December) |
| Month, numeric (00..12) |
| AM or PM |
| Time, 12-hour (hh:mm:ss followed by AM or PM) |
| Seconds (00..59) |
| Seconds (00..59) |
| Time, 24-hour (hh:mm:ss) |
| Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 |
| Week (00..53), where Monday is the first day of the week; WEEK() mode 1 |
Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X | |
| Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x |
| Weekday name (Sunday..Saturday) |
| Day of the week (0=Sunday..6=Saturday) |
| Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
| Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
| Year, numeric, four digits |
| Year, numeric (two digits) |
A literal % character |
