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 MySQL DATE GET_FORMAT function in a combination of DATE_FORMAT and STR_TO_DATE functions. The basic syntax of the GET_FORMAT() Function in MySQL is as shown below:

GET_FORMAT(Date/Time/DateTime, format);

MySQL Get Format options

The following are the list of MySQL Get format ISO specifiers that are available to format Date and time.

MySQL GET_FORMAT Function 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 1

Here, We used the DATE_FORMAT function. Inside that function, we used this GET_FORMAT function. From the below MySQL code snippet, you can see, 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'));
MySQL GET_FORMAT Function 1

In this example, we are using 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, given Date string is in USA format. So, this Date Function converts that string to Date using STR_TO_DATE function.

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

Please refer to DATE_FORMAT and STR_TO_DATE functions.