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