MySQL GET_FORMAT function is one of the Date Functions used to return the formatted string of date and time value. Generally, we use this GET_FORMAT function in a combination of DATE_FORMAT and STR_TO_DATE functions, and the basic syntax of this is as shown below:
GET_FORMAT(Date/Time/DateTime, format);
MySQL Get Format options
The following is the list of ISO format specifiers available to format Date and time.
| GET_FORMAT Call | Result |
|---|---|
| GET_FORMAT(DATE, ‘USA’) | ‘%m.%d.%Y’ |
| GET_FORMAT(DATE, ‘JIS’) | ‘%Y-%m-%d’ |
| GET_FORMAT(DATE, ‘ISO’) | ‘%Y-%m-%d’ |
| GET_FORMAT(DATE, ‘EUR’) | ‘%d.%m.%Y’ |
| GET_FORMAT(DATE, ‘INTERNAL’) | ‘%Y%m%d’ |
| 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, ‘EUR’) | ‘%Y-%m-%d %H.%i.%s’ |
| GET_FORMAT(DATETIME, INTERNAL’) | ‘%Y%m%d%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, ‘EUR’) | ‘%H.%i.%s’ |
| GET_FORMAT(TIME, ‘INTERNAL’) | ‘%H%i%s’ |
MySQL GET_FORMAT Function Example
Here, We used the DATE_FORMAT method. Inside that, we used this method. From the below MySQL code snippet, you can see that we used USA, EUR, and INTERNAL as the second arguments.
SELECT DATE_FORMAT('2019-02-28', GET_FORMAT(DATE, 'USA'));
SELECT DATE_FORMAT('2019-02-28', GET_FORMAT(DATE, 'EUR'));
SELECT DATE_FORMAT('2019-02-28', GET_FORMAT(DATE, 'INTERNAL'));
In this example, we are using the GET_FORMAT function with a combination of STR_TO_DATE. By this, you can specify from which format of String you are converting into a Date.
For example, Within the First statement, the given string is in USA format. So, this Date method converts that string to Date using the STR_TO_DATE method.
Please refer to DATE_FORMAT and STR_TO_DATE methods.
SELECT STR_TO_DATE('01.31.2019', GET_FORMAT(DATE, 'USA'));
SELECT STR_TO_DATE('31.12.2018', GET_FORMAT(DATE, 'EUR'));
SELECT DATE_FORMAT('2019-02-28 10:22:33.999998', GET_FORMAT(TIME, 'EUR'));
