MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL GET_FORMAT() Function



The MySQL GET_FORMAT() function returns a format string. This function is useful in combination with the DATE_FORMAT() and the STR_TO_DATE() functions.

Possible result formats are:

Function CallResult Format
GET_FORMAT(DATE,'EUR')'%d.%m.%Y'
GET_FORMAT(DATE,'USA')'%m.%d.%Y'
GET_FORMAT(DATE,'JIS')'%Y-%m-%d'
GET_FORMAT(DATE,'ISO')'%Y-%m-%d'
GET_FORMAT(DATE,'INTERNAL')'%Y%m%d'
GET_FORMAT(DATETIME,'EUR')'%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'USA')'%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'JIS')'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'ISO')'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'INTERNAL')'%Y%m%d%H%i%s'
GET_FORMAT(TIME,'EUR')'%H.%i.%s'
GET_FORMAT(TIME,'USA')'%h:%i:%s %p'
GET_FORMAT(TIME,'JIS')'%H:%i:%s'
GET_FORMAT(TIME,'ISO')'%H:%i:%s'
GET_FORMAT(TIME,'INTERNAL')'%H%i%s'

Example:

The example below shows the usage of GET_FORMAT() function.

mysql> SELECT GET_FORMAT(DATE, 'EUR');
Result: '%d.%m.%Y'

mysql> SELECT GET_FORMAT(DATETIME, 'USA');
Result: '%Y-%m-%d %H.%i.%s'

mysql> SELECT GET_FORMAT(DATETIME, 'INTERNAL');
Result: '%Y%m%d%H%i%s'

mysql> SELECT GET_FORMAT(TIME, 'ISO');
Result: '%H:%i:%s'

mysql> SELECT DATE_FORMAT('2018-08-18', GET_FORMAT(DATE,'USA'));
Result: '08.18.2018'

mysql> SELECT STR_TO_DATE('08.18.2018', GET_FORMAT(DATE,'USA'));
Result: '2018-08-18'

Example 2:

Consider a database table called Employee with the following records:

EmpIDNameCityAgeDate of Joining
1JohnLondon252018-05-25
2MarryNew York242018-10-15
3JoParis272019-06-09
4KimAmsterdam302019-09-21
5RameshNew Delhi282019-10-25

In the query below, the DATE_FORMAT() function is used to format the date value of [Date of Joining] column:

SELECT *, 
DATE_FORMAT([Date of Joining], GET_FORMAT(DATE, 'USA')) AS DATE_FORMAT_Value
FROM Employee;

This will produce a result similar to:

EmpIDNameCityAgeDate of JoiningDATE_FORMAT_Value
1JohnLondon252018-05-2505.25.2018
2MarryNew York242018-10-1510.15.2018
3JoParis272019-06-0906.09.2019
4KimAmsterdam302019-09-2109.21.2019
5RameshNew Delhi282019-10-2510.25.2019

❮ MySQL Functions

5