MySQL GET_FORMAT() Function

Summary: in this tutorial, you will learn how to use the MySQL GET_FORMAT() function to return a format string.

Introduction to MySQL GET_FORMAT() function

The GET_FORMAT() function lets you get a format string of a DATE, TIME, DATETIME, or TIMESTAMP.

Here’s the syntax of the GET_FORMAT() function:

GET_FORMAT(
   {DATE|TIME|DATETIME|TIMESTAMP}, 
   {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'}
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The first argument specifies the date and time you want to get the format string. It can be DATE, TIME, DATETIME, or TIMESTAMP.
• The second argument determines the desired format style ‘EUR‘, ‘USA‘, ‘JIS‘, ‘ISO‘, and ‘INTERNAL‘

Note that the  ISO format refers to ISO 9075, not ISO 8601.

The GET_FORMAT() function returns a format string.

In practice, you use the GET_FORMAT() function with the DATE_FORMAT() and STR_TO_DATE() functions to format and parse dates and times in different styles.

MySQL GET_FORMAT() function examples

Let’s take some examples of using the DATE_FORMAT() function.

1) Using GET_FORMAT() function with DATE example

The following example uses the GET_FORMAT() function with the DATE_FORMAT() function to format a date in European style:

SELECT 
  DATE_FORMAT(
    '2023-10-19', 
    GET_FORMAT(DATE, 'EUR')
  ) AS formatted_date;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+
| formatted_date |
+----------------+
| 19.10.2023     |
+----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we used the GET_FORMAT() function to get the date format in European style and the returned format string to the DATE_FORMAT() function to format the date '2023-10-19'.

2) Using GET_FORMAT() function with TIME example

The following example uses the GET_FORMAT() function with TIME_FORMAT() function to format a time value:

SELECT 
  TIME_FORMAT(
    '14:30:15', 
    GET_FORMAT(TIME, 'INTERNAL')
  ) AS formatted_time;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+
| formatted_time |
+----------------+
| 143015         |
+----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Using GET_FORMAT() function with DATETIME example

The following example uses the GET_FORMAT() function to format a datetime value in ISO9075 style:

SELECT 
  DATE_FORMAT(
    '2023-10-19 15:30:00', 
    GET_FORMAT(DATETIME, 'ISO')
  ) AS formatted_datetime;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| formatted_datetime  |
+---------------------+
| 2023-10-19 15:30:00 |
+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Using GET_FORMAT() function with TIMESTAMP example

The following example uses the GET_FORMAT() function with TIMESTAMP value to format timestamps in USA style:

SELECT 
  DATE_FORMAT(
    '2023-10-19 15:30:00', 
    GET_FORMAT(TIMESTAMP, 'USA')
  ) AS formatted_timestamp;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| formatted_timestamp |
+---------------------+
| 2023-10-19 15.30.00 |
+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the GET_FORMAT() function to get a format string for a DATE, TIME, DATETIME, and TIMESTAMP.