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

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.