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.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

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.

Frequently Asked Questions (FAQs)

Can I format NULL dates?

DATE_FORMAT() returns NULL when the input value is NULL. Use COALESCE() to provide a fallback label.

Is DATE_FORMAT() expensive?

It runs per row, so large reports can slow down. Cache results or format in application code for huge datasets.

Does MySQL support timezone conversion inside DATE_FORMAT()?

DATE_FORMAT() shows the value as stored. Wrap the column with CONVERT_TZ() first when you need timezone adjustments.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie