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 Call	Result 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:

EmpID	Name	City	Age	Date_of_Joining
1	John	London	25	2018-05-25
2	Marry	New York	24	2018-10-15
3	Jo	Paris	27	2019-06-09
4	Kim	Amsterdam	30	2019-09-21
5	Ramesh	New Delhi	28	2019-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:

EmpID	Name	City	Age	Date_of_Joining	DATE_FORMAT_Value
1	John	London	25	2018-05-25	05.25.2018
2	Marry	New York	24	2018-10-15	10.15.2018
3	Jo	Paris	27	2019-06-09	06.09.2019
4	Kim	Amsterdam	30	2019-09-21	09.21.2019
5	Ramesh	New Delhi	28	2019-10-25	10.25.2019

❮ MySQL Functions