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
, orTIMESTAMP
. - 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 aDATE
,TIME
,DATETIME
, andTIMESTAMP
.