SQL DATE_FORMAT
What is SQL DATE_FORMAT?
The SQL DATE_FORMAT function is specific to MySQL and is used to format date and time values into a specified text format. It transforms date and time data into human-readable strings according to a defined format pattern.
When you would use it
You would use the DATE_FORMAT function in MySQL when you need to:
-
Customize Date Presentation: Format date and time values to be more human-friendly, following a specific pattern or style, such as "MM/DD/YYYY" or "YYYY-MM-DD HH:MM:SS."
-
Generate Reports: Prepare data for reports, documents, or presentations by converting date and time data into a format suitable for presentation.
-
Display Dates in Queries: Make query results more understandable and visually appealing by displaying date values in a specific format.
Syntax
The syntax for the SQL DATE_FORMAT function in MySQL is as follows:
DATE_FORMAT(date, format_pattern)
date: The date or timestamp value you want to format.format_pattern: The pattern that specifies how the date should be formatted.
Parameter values
date: The date or timestamp value that you want to format.format_pattern: The pattern that defines how the date should appear. The format pattern can include various placeholders for different components of the date, such as year, month, day, hour, minute, and second.
Example query
Here's an example SQL query that uses the DATE_FORMAT function to format a date from a "transaction_date" column in a table named "transactions" in MySQL:
SELECT
transaction_id,
transaction_amount,
DATE_FORMAT(transaction_date, '%Y-%m-%d %H:%i:%s') AS formatted_date
FROM transactions;
Example table response
The example query formats the "transaction_date" into a custom pattern and retrieves the transaction ID, amount, and formatted date. The resulting table response will look like this:
| transaction_id | transaction_amount | formatted_date |
|---|---|---|
| 1 | 100.00 | 2023-10-19 15:25:45 |
| 2 | 75.50 | 2023-09-05 08:30:15 |
| 3 | 50.25 | 2023-08-15 14:10:30 |
Use cases
-
Customize Date Presentation: To make date and time values more readable by formatting them in a way that suits your requirements, corporate standards, or user preferences, especially in MySQL.
-
Generate Reports: When generating reports or documents, format date and time values to meet the presentation style of your reports, specifically in MySQL.
-
Display Dates in Queries: For improved readability of query results by showing dates and times in a specific format, particularly in MySQL.
SQL Languages Availability
The SQL DATE_FORMAT function is specific to MySQL and is not available in all SQL database management systems. It is a MySQL-specific function for date formatting. If you are using MySQL, you can utilize this function for date formatting. Other database systems may have similar but different functions for date formatting.
