MySQL GET_FORMAT Function

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 CallResult
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'));
Example 1

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'));
MySQL GET_FORMAT Function 2