MySQL DATE_FORMAT Function

MySQL DATE_FORMAT() Function

The DATE_FORMAT() function in MySQL is used to format a date or datetime value into a custom string based on a specified format. This is particularly useful when you need to display dates in a user-friendly format or adhere to specific formatting requirements.

Syntax

DATE_FORMAT(date, format)

date: The date or datetime value to format.
format: A string that defines the desired output format using predefined format specifiers.

Format Specifiers

Specifier	Description	Example Output
`%Y`	Year (4 digits)	`2025`
`%y`	Year (2 digits)	`25`
`%M`	Full month name	`January`
`%m`	Month (2 digits)	`01`
`%b`	Abbreviated month name	`Jan`
`%d`	Day of the month (2 digits)	`12`
`%e`	Day of the month (1-31, no leading 0)	`12`
`%W`	Full weekday name	`Sunday`
`%a`	Abbreviated weekday name	`Sun`
`%H`	Hour (24-hour format, 2 digits)	`14`
`%h`	Hour (12-hour format, 2 digits)	`02`
`%i`	Minutes (2 digits)	`30`
`%s`	Seconds (2 digits)	`45`
`%p`	AM or PM	`PM`
`%T`	Time (HH:MM:SS)	`14:30:45`
`%r`	Time (12-hour format)	`02:30:45 PM`
`%j`	Day of the year (001-366)	`012`
`%U`	Week number (Sunday as the first day)	`02`
`%u`	Week number (Monday as the first day)	`02`

Examples

1. Basic Usage

Format a date into DD-MM-YYYY:

SELECT DATE_FORMAT('2025-01-12', '%d-%m-%Y') AS formatted_date;

Output:

12-01-2025

2. Formatting with Month and Year

Format a date to display the full month name and year:

SELECT DATE_FORMAT('2025-01-12', '%M %Y') AS formatted_date;

Output:

January 2025

3. Formatting a Datetime

Format a datetime to show a 12-hour clock with AM/PM:

SELECT DATE_FORMAT('2025-01-12 14:30:45', '%h:%i:%s %p') AS formatted_time;

Output:

02:30:45 PM

4. Combining Date and Time

Display a full date and time in a readable format:

SELECT DATE_FORMAT('2025-01-12 14:30:45', '%W, %M %d, %Y at %h:%i %p') AS formatted_datetime;

Output:

Sunday, January 12, 2025 at 02:30 PM

5. Using NOW() with DATE_FORMAT()

Format the current date and time:

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

Output:

2025-01-12 14:30:45

6. Formatting Week Numbers

Get the week number for a specific date:

SELECT DATE_FORMAT('2025-01-12', '%U') AS week_number_sunday,
       DATE_FORMAT('2025-01-12', '%u') AS week_number_monday;

Output:

week_number_sunday: 02
week_number_monday: 02

Practical Applications

User-Friendly Date Display: Format dates for reports, dashboards, or web pages.

SELECT DATE_FORMAT(order_date, '%M %d, %Y') AS formatted_order_date FROM orders;

Localization: Display dates in local formats (e.g., DD/MM/YYYY for European regions).
```
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y') AS european_date;
```

Custom Timestamping: Combine date and time with specific formatting.

SELECT DATE_FORMAT(NOW(), 'Generated on %W, %M %d, %Y at %h:%i:%s %p') AS timestamp;

Key Points to Remember

Invalid Dates: If an invalid date is passed to DATE_FORMAT(), it returns NULL.
No Timezone Adjustments: The function works based on the server's timezone settings. Use CONVERT_TZ() for timezone conversions.
Efficient for Display: Use DATE_FORMAT() for display purposes, but keep the original datetime format in your database for accurate calculations and indexing.

Conclusion

The DATE_FORMAT() function is a powerful and flexible tool for customizing how dates and times are displayed in MySQL. By leveraging its wide range of format specifiers, you can create tailored date outputs to meet various regional, functional, and business needs.

Souy Soeng

MySQL DATE_FORMAT Function