Skip to main content
Log inGet a demo
Back to SQL Dictionary
Date functions

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:

  1. 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."

  2. Generate Reports: Prepare data for reports, documents, or presentations by converting date and time data into a format suitable for presentation.

  3. 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_idtransaction_amountformatted_date
1100.002023-10-19 15:25:45
275.502023-09-05 08:30:15
350.252023-08-15 14:10:30

Use cases

  1. 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.

  2. Generate Reports: When generating reports or documents, format date and time values to meet the presentation style of your reports, specifically in MySQL.

  3. 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.

Related

SQL DATEDIFF

SQL UNIQUE

SQL PRIMARY KEY

Ready to put your SQL knowledge to work?

Practice writing SQL to call data from the warehouse and sync it into Google Sheets in this 5 minute interactive demo.

Hightouch Audiences user interface.