In this tutorial, we will learn about the MySQL GET_FORMAT() function. Before you proceed with this article, I would highly encourage you to read the DATE_FORMAT() article as this tutorial builds on concepts learnt in that article.
Suppose you have a list of joining dates of employees in your company. These dates have been stored in a MySQL table in the format YYYY-MM-DD. Your manager has asked you to display the employee details like id, name, department and the joining date in the European format. Now, you must be wondering what does “date in European format” even mean? This is where the MySQL GET_FORMAT() function comes into play.
The MySQL GET_FORMAT() function is used to convert a date/time/datetime value in a formatted manner based on the standard format mentioned as the second argument. Let us understand this better with the syntax and some examples.
Syntax of MySQL GET_FORMAT()
GET_FORMAT(datatype, format)Code language: SQL (Structured Query Language) (sql)Where,
- ‘datatype’ can be one of – DATE or TIME or DATETIME or TIMESTAMP, and
- ‘format’ can be one of ‘EUR’, ‘USA’, ’JIS’, ’ISO’ or ‘INTERNAL’.
- ‘USA’ refers to the American format. ‘EUR’ refers to the European format, ‘ISO’ refers to the ISO 9075 format. ‘JIS’ refers to the Japanese Industrial Standard format.
Examples of MySQL GET_FORMAT()
MySQL GET_FORMAT() With the Date Datatype
Let us see the different date formats under the specified formats. Let us use the SELECT statement and aliases to help make our output readable.
SELECT GET_FORMAT(DATE, 'USA') AS USA,
GET_FORMAT(DATE, 'JIS') AS JIS,
GET_FORMAT(DATE, 'EUR') AS Europe,
GET_FORMAT(DATE, 'ISO') AS ISO,
GET_FORMAT(DATE, 'INTERNAL') AS INTERNAL;Code language: SQL (Structured Query Language) (sql)And the output is –
How to use GET_FORMAT() to format dates?
Let us see the practical application of the GET_FORMAT() function with the DATE_FORMAT() function. Let us write a query in which we convert the date – ‘2021-04-12’ to the American format. The query is –
SELECT DATE_FORMAT('2021-04-12', GET_FORMAT(DATE, 'USA')) AS 'Date In American Format';Code language: SQL (Structured Query Language) (sql)And the output is –
Similarly, let us write a query which converts the same date to the European format. The query is –
SELECT DATE_FORMAT('2021-04-12', GET_FORMAT(DATE, 'EUR')) AS 'Date In European Format';Code language: SQL (Structured Query Language) (sql)And the output is –
GET_FORMAT() With the Datetime Datatype
Next, let us see the different datetime formats under the specified formats. The query to see these formats is –
SELECT GET_FORMAT(DATETIME, 'USA') AS USA,
GET_FORMAT(DATETIME, 'JIS') AS JIS,
GET_FORMAT(DATETIME, 'EUR') AS Europe,
GET_FORMAT(DATETIME, 'ISO') AS ISO,
GET_FORMAT(DATETIME, 'INTERNAL') AS INTERNAL;Code language: SQL (Structured Query Language) (sql)And the output is –
How to use GET_FORMAT() to format datetime values?
Let us see the practical application of the GET_FORMAT() function with the DATE_FORMAT() function. Let us write a query in which we convert the current datetime value to the ISO format. We will use the NOW() function to get the current datetime value. The query is –
SELECT NOW(), DATE_FORMAT(NOW(), GET_FORMAT(DATETIME, 'ISO')) AS 'Date In ISO Format';Code language: SQL (Structured Query Language) (sql)And the output is –
Similarly, let us format the current datetime value to the European format. The query for that is:
SELECT NOW(), DATE_FORMAT(NOW(), GET_FORMAT(DATETIME, 'EUR')) AS 'Date In European Format';Code language: SQL (Structured Query Language) (sql)And the output is –
Finally, let us format the current datetime value to the ‘INTERNAL’ format. The query for that is:
SELECT NOW(), DATE_FORMAT(NOW(), GET_FORMAT(DATETIME, 'INTERNAL')) AS 'Date In Internal Format';Code language: SQL (Structured Query Language) (sql)And the output is –
Working With Tables
Consider the below Employee table.
Let us come back to the problem I mentioned in the beginning. Suppose you have the above Employee table. Your manager has asked you to display the employee details like id, name, department and the joining date in the European format. Now that you are familiar with the MySQL GET_FORMAT() function, this should be easy. The query is –
SELECT eid, Name, Department, DATE_FORMAT(Date_Joined, GET_FORMAT(DATE, 'EUR')) AS 'Joining Date' FROM Employee;Code language: SQL (Structured Query Language) (sql)And the output is –
Conclusion
Formatting date and time values is a very important operation in MySQL and is widely used in a lot of use cases – especially when we have to make the date and time value presentable for the user. I would highly encourage you to practice a couple of examples of this function.
References
- MySQL Official documentation on the
GET_FORMAT()function.