How to Format Dates in MySQL

Galaxy Glossary

How do I format a date in MySQL?

DATE_FORMAT() turns DATE, DATETIME, or TIMESTAMP values into strings in any layout you need.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does DATE_FORMAT() do?

DATE_FORMAT() converts a date or timestamp into a string based on a format mask. Use it when you need readable dates in reports, exports, or UIs.

How does DATE_FORMAT() syntax work?

Provide the date column first, then a format string containing % tokens.MySQL replaces each token with the matching date part.

Which format specifiers are available?

%Y = four-digit year, %y = two-digit year, %m = zero-padded month, %c = numeric month, %M = full month name, %d = zero-padded day, %H = 24-hour, %i = minute, %s = second.

How to format order dates for invoices?

SELECT id, DATE_FORMAT(order_date,'%M %d, %Y') AS invoice_date FROM Orders;

How to display month names only?

SELECT DISTINCT DATE_FORMAT(order_date,'%M') AS order_month FROM Orders ORDER BY 1;

How to convert timestamps to ISO 8601?

SELECT DATE_FORMAT(NOW(),'%Y-%m-%dT%H:%i:%sZ') AS iso_time;

Best practices for date formatting

Always store raw DATE/DATETIME values; format them only in SELECTs.Document your masks in code reviews and keep formatting logic in one place.

Common mistakes and fixes

Using mm instead of %m yields NULL—always prefix tokens with %. Avoid persisting formatted strings; use triggers or app code for presentation.

Can I localize date formats in MySQL?

MySQL 8.0+ supports LOCALE in DATE_FORMAT(): DATE_FORMAT(order_date, '%d %b %Y' LOCALE 'fr_FR') prints French month names.

Why How to Format Dates in MySQL is important

How to Format Dates in MySQL Example Usage


-- Show each customer's last purchase in "15-Mar-2024" style
SELECT c.name,
       DATE_FORMAT(MAX(o.order_date),'%d-%b-%Y') AS last_purchase
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

How to Format Dates in MySQL Syntax


DATE_FORMAT(date_value, 'format_mask' [LOCALE 'locale_name'])
-- Basic example
SELECT DATE_FORMAT(order_date,'%Y-%m-%d') AS order_day
FROM Orders;

-- Detailed ecommerce invoice
SELECT o.id,
       c.name,
       DATE_FORMAT(o.order_date,'%M %d, %Y at %H:%i') AS order_time,
       FORMAT(o.total_amount,2) AS total
FROM Orders o
JOIN Customers c ON c.id = o.customer_id;

Common Mistakes

Using lowercase mm or dd instead of %m and %d: MySQL treats mm/dd as literal characters, returning NULL. Always include % before each specifier.
Storing formatted strings in tables: this breaks sorting and indexing. Keep original DATE/DATETIME types and format only when selecting.