MySQL | GET_FORMAT method
Start your free 7-days trial now!
MySQL's GET_FORMAT(~) method will return a format string based on the default formatting style for the input region.
Parameters
1. DATE | TIME | DATETIME
The data type to retrieve the format string for.
2. 'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL'
The region whose formatting style we should return. Note ISO here refers to ISO 9075.
Return value
The below table shows the possible method calls, together with their corresponding return format string:
Method call | Return value |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Examples
Basic usage
To return format string for ISO 9075 date:
SELECT GET_FORMAT(DATE,'ISO');
+------------------------+| GET_FORMAT(DATE,'ISO') |+------------------------+| %Y-%m-%d |+------------------------+
Usage with DATE_FORMAT(~)
To use the return value of GET_FORMAT(~) as an input for DATE_FORMAT(~)
SELECT DATE_FORMAT('2020-04-26 21:53:10', GET_FORMAT(DATE, 'USA'));
+-------------------------------------------------------------+| DATE_FORMAT('2020-04-26 21:53:10', GET_FORMAT(DATE, 'USA')) |+-------------------------------------------------------------+| 04.26.2020 |+-------------------------------------------------------------+
Note that the return value of GET_FORMAT(DATE,'USA') is '%m.%d.%Y', which specifies the format we should use to format '2020-04-26 21:53:00'.
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 |
