The DATE_FORMAT function in MySQL is a powerful tool for formatting date and time values according to a specified format. This function is particularly useful when you need to present date and time information in a customized way, such as changing the order of elements or including additional text.

Syntax

Here is the basic syntax of the DATE_FORMAT function:

DATE_FORMAT(date, format)

date: This is the date or datetime value that you want to format.
format: This is a string that specifies the desired format for the output.

The DATE_FORMAT function supports a variety of format specifiers, each representing a different component of the date and time. Some common format specifiers include:

%Y: Four-digit year (e.g., 2022).
%m: Two-digit month (01 to 12).
%d: Two-digit day of the month (01 to 31).
%H: Two-digit hour in 24-hour format (00 to 23).
%i: Two-digit minutes (00 to 59).
%s: Two-digit seconds (00 to 59).
%p: AM or PM for time values.

Example

Here’s an example of using the DATE_FORMAT function:

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_datetime;

In this example, the NOW function returns the current date and time, and the DATE_FORMAT function formats it as ‘YYYY-MM-DD HH:MI:SS’. The result would look something like ‘2024-01-01 12:34:56’, depending on the current date and time.

Additionally, you can use the DATE_FORMAT function with columns in a table to retrieve formatted date and time values from your database.

SELECT 
event_name, 
DATE_FORMAT(event_date, '%M %d, %Y') AS formatted_date
FROM events;

In this query, event_name is selected along with the event_date column formatted as ‘Month day, Year’. This makes the output more readable and user-friendly.

In summary, the DATE_FORMAT function in MySQL is a valuable tool for customizing the display of date and time values, providing flexibility in how you present temporal information in your SQL queries and applications.