MySQL DATE_FORMAT() Function

The `DATE_FORMAT()` function in MySQL is used to format a date as specified by a format string. It allows you to convert date and time data into a human-readable format or a specific format required by your application.

Usage

The `DATE_FORMAT()` function is employed when you need to display date and time in a customized format. It takes a date as its first argument and a format string as its second argument.

DATE_FORMAT(date, format)

In this syntax, `date` is the date to be formatted, and `format` is a string containing the format specifiers that define the output format.

Common Format Specifiers

• %Y: Year, numeric, four digits
• %m: Month, numeric (00..12)
• %d: Day of the month, numeric (00..31)
• %H: Hour (00..23)
• %i: Minutes, numeric (00..59)
• %p: AM or PM

Examples

1. Basic Date Formatting

To format a date in the standard MySQL date format:

SELECT DATE_FORMAT('2023-12-31', '%Y-%m-%d');

This example formats the date `'2023-12-31'` as `2023-12-31`, illustrating the basic use of `DATE_FORMAT()` without altering the format.

2. Custom Date Display

To display a date with full day and month names:

SELECT DATE_FORMAT('2023-12-31', '%W, %M %e, %Y');

This formats the date as `Sunday, December 31, 2023`, showcasing the usage of format specifiers to display the full name of the day, month, and the day of the month.

3. Date and Time Formatting

To customize date and time presentation:

SELECT DATE_FORMAT('2023-12-31 15:45:30', '%d-%b-%Y %h:%i %p');

Here, the date and time `'2023-12-31 15:45:30'` are formatted as `31-Dec-2023 03:45 PM`, demonstrating the ability to format both date and time components using various specifiers.

Error Handling

If an incorrect format string is provided, `DATE_FORMAT()` may return unexpected results or NULL. Ensure format strings are validated to prevent errors.

Tips and Best Practices

• Familiarize with format specifiers. Understand the available specifiers for accurate formatting.
• Ensure format consistency. Maintain consistent date formats across your application.
• Mind Locale and Time Zones. Consider locale differences and time zones.
• Test formats thoroughly. Validate formats with various date inputs to ensure correct rendering.