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. Also, read about the MySQL STR_TO_DATE function.
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.
TIP: Please refer to the MySQL DATE_FORMAT and MySQL TIME_FORMAT methods from the list of available MySQL Date functions.
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'));
