How to Format Dates in MySQL
Last updated August 26, 2026 · By the SaturnSQL team
Use DATE_FORMAT(date, format) with % specifiers: '%Y-%m-%d' gives 2026-07-25, '%M %e, %Y' gives July 25, 2026. DATE_FORMAT returns a string, so use it for display and grouping labels, not for comparisons on indexed columns.
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS day,
DATE_FORMAT(created_at, '%M %e, %Y') AS pretty,
DATE_FORMAT(created_at, '%H:%i') AS hhmm
FROM orders;Common specifiers
%Y four-digit year, %m month 01-12, %d day 01-31, %H hour 00-23, %i minutes, %s seconds, %M month name, %W weekday name, %e day without leading zero.
Group by month
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;%m is the month, %i is the minute
DATE_FORMAT uses %i for minutes, not %m, and writing %H:%m quietly gives you the hour followed by the month. Nothing warns you, and the output still looks like a time, which is what makes it hard to spot in a report.
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') AS correct,
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%m') AS repeats_the_month;Do not format a column you are filtering on
Wrapping the column in DATE_FORMAT to compare it against a string makes the index on that column unusable, so MySQL scans the table. Compare on the raw column with a half-open range and format only in the SELECT list.
-- scans
SELECT * FROM orders WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-08';
-- uses the index
SELECT * FROM orders
WHERE created_at >= '2026-08-01' AND created_at < '2026-09-01';Common errors
Warning 1411 Incorrect datetime value: '31/08/2026' for function str_to_date
The string does not match the pattern you passed, so STR_TO_DATE returns NULL and reports a warning rather than failing. A query that silently produces NULL dates is the usual symptom.
Match the pattern to the data. Run SHOW WARNINGS after a load to catch this, because nothing else surfaces it.
SELECT STR_TO_DATE('31/08/2026', '%d/%m/%Y');
SHOW WARNINGS;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free