How to Use DATE_FORMAT in MySQL: Syntax and Examples

Use the DATE_FORMAT(date, format) function in MySQL to display a date in a specific format. The date is the date value, and format is a string that defines how the date should appear using format specifiers like %Y for year or %m for month.

Syntax

The DATE_FORMAT function takes two arguments:

• date: The date or datetime value you want to format.
• format: A string that specifies the output format using format specifiers.

Format specifiers start with % and represent parts of the date, such as year, month, day, hour, minute, and second.

sql

DATE_FORMAT(date, format)

Example

This example shows how to format the current date and time to display as 'Day-Month-Year Hour:Minute:Second'.

sql

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

Output

formatted_date
-------------------
27-04-2024 15:30:45

Common Pitfalls

Common mistakes include:

• Using incorrect format specifiers (e.g., %M for month name vs %m for month number).
• Confusing %i (minutes) with %m (month).
• Not enclosing the format string in single quotes.

Always check the format specifiers carefully to get the expected output.

sql

/* Wrong: Using %M for month number (actually month name) */
SELECT DATE_FORMAT(NOW(), '%d-%M-%Y') AS wrong_format;

/* Right: Use %m for month number */
SELECT DATE_FORMAT(NOW(), '%d-%m-%Y') AS correct_format;

Output

wrong_format
--------------
27-April-2024

correct_format
--------------
27-04-2024

Quick Reference

Format Specifier	Description	Example Output
%Y	4-digit year	2024
%y	2-digit year	24
%m	Month number (01-12)	04
%M	Full month name	April
%d	Day of month (01-31)	27
%H	Hour (00-23)	15
%h	Hour (01-12)	03
%i	Minutes (00-59)	30
%s	Seconds (00-59)	45
%p	AM or PM	PM

Key Takeaways

Use DATE_FORMAT(date, format) to display dates in custom formats in MySQL.

Format specifiers like %Y, %m, %d control how each part of the date appears.

Always enclose the format string in single quotes.

Be careful to use correct specifiers; %i is minutes, %m is month number.

DATE_FORMAT works with date and datetime values to improve readability.