MySQL DATE_FORMAT: Format Dates for Display, Not for Storage

DATE_FORMAT(date, format) turns a MySQL date or datetime into a formatted string. DATE_FORMAT(NOW(), '%Y-%m-%d') gives something like 2026-07-18. The format string uses specifiers - %Y for a four-digit year, %m for a zero-padded month, %d for the day. The rule that saves you real pain: use DATE_FORMAT for output only. Store dates in DATE or DATETIME columns, never as pre-formatted strings, and format them at the moment you display them. Storing formatted dates is how you lose sorting, comparison, and date math.

Table of contents

• The syntax and the common specifiers
• Format for display, store as a real date
• DATE_FORMAT versus STR_TO_DATE
• Locale, week numbers, and the tricky specifiers
• Common patterns you will reuse
• How this fits the rest of the stack
• FAQ

The syntax and the common specifiers

SELECT DATE_FORMAT('2026-07-18', '%Y-%m-%d');   -- 2026-07-18
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');          -- 18/07/2026
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');     -- Saturday, July 18, 2026

The specifiers you will use constantly:

• %Y - four-digit year (2026); %y - two-digit (26)
• %m - month, zero-padded (07); %c - month, no padding (7)
• %d - day, zero-padded (08); %e - day, no padding (8)
• %H - hour 00-23; %h - hour 01-12; %i - minutes; %s - seconds
• %p - AM/PM; %M - full month name; %W - full weekday name

Case matters and it is not intuitive: %m is the numeric month but %M is the month name, %d is the day number but %D is the day with an ordinal suffix. Mixing up the case is the most common DATE_FORMAT mistake - always check whether you meant the number or the name.

Format for display, store as a real date

This is the point that matters more than any specifier. Keep dates in proper date columns:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    created_at DATETIME     -- a real date type, not VARCHAR
);

Then format only when you present the value:

SELECT id, DATE_FORMAT(created_at, '%M %e, %Y') AS created
FROM orders;

Why it matters: a DATETIME sorts chronologically, compares with < and >, and works with date functions. A date stored as the string 18/07/2026 sorts alphabetically - so 01/12/2025 comes after 18/07/2026 - cannot be compared as a date, and breaks every date calculation.

Store the machine-friendly value, format the human-friendly one at the edge. This one discipline prevents a whole category of sorting and filtering bugs that are miserable to fix after the data is already stored wrong.

DATE_FORMAT versus STR_TO_DATE

DATE_FORMAT goes date to string. Its inverse, STR_TO_DATE, goes string to date - for parsing input:

DATE_FORMAT('2026-07-18', '%d/%m/%Y')          -- date -> '18/07/2026' (string)
STR_TO_DATE('18/07/2026', '%d/%m/%Y')          -- string -> a real DATE

STR_TO_DATE is what you use when data arrives as text in a particular layout and you need to turn it into a real date to store or compare. The format string describes the shape of the input so MySQL knows how to read it.

Together they bracket the flow: parse incoming text with STR_TO_DATE on the way in, store it as a DATE/DATETIME, and render it with DATE_FORMAT on the way out. If you find yourself using DATE_FORMAT to reformat something that is already a string, you probably stored a date as text - the fix is to store it properly, not to reformat the string.

Locale, week numbers, and the tricky specifiers

A few specifiers behave in ways worth knowing before they surprise you.

Month and weekday names follow the connection’s locale setting (lc_time_names). By default it is English, so %M gives July. Change the locale and the same query returns the localized name:

SET lc_time_names = 'fr_FR';
SELECT DATE_FORMAT(NOW(), '%M');   -- juillet

Week numbering is genuinely tricky - %U, %u, %V, %v differ on whether the week starts Sunday or Monday and on how the first week of the year is defined. If you need ISO week numbers, %v (with %x for the ISO year) is the pair that follows the ISO-8601 rule. Do not guess here; check which specifier matches your definition of a week, because getting it wrong produces off-by-one week numbers around New Year that are hard to spot.

Common patterns you will reuse

A handful of formats cover most real needs:

-- ISO 8601, sortable, unambiguous
DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')      -- 2026-07-18 14:30:00

-- Friendly, US
DATE_FORMAT(created_at, '%M %e, %Y')              -- July 18, 2026

-- Friendly, European
DATE_FORMAT(created_at, '%e %M %Y')               -- 18 July 2026

-- Just the month for grouping reports
DATE_FORMAT(created_at, '%Y-%m')                  -- 2026-07

That last one is a quiet workhorse - grouping by DATE_FORMAT(created_at, '%Y-%m') buckets rows by month for a report, and because the format sorts correctly as a string (year first), the grouping stays chronological.

Keep the ISO pattern (%Y-%m-%d) as your default whenever the output is for another machine or needs to sort, and switch to the named-month formats only for human-facing display. Machine-sortable by default, pretty on demand - that habit keeps date output predictable.

How this fits the rest of the stack

Storing dates as real types and formatting them only for display is a schema-discipline decision that pays off every time you sort, filter, or report - and it is far cheaper to get right at design time than to migrate later. When that database is a managed service behind an app, clean date handling is one less thing to untangle under production load. The RunxBuild hosting calculator lays out the service, database, storage, and bandwidth as separate line items, and the RunxBuild dashboard is where the team watches deploys, logs, and restarts as they happen.

Useful related references:

• AWS S3 Pricing Calculator: Storage, Requests, and Data Transfer
• Azure Blob Storage Costs: The Storage Price Is the Small Part
• Azure S3 Equivalent: Blob Storage, Tiers, and S3 Compatibility
• Managed databases on RunxBuild

FAQ

What does DATE_FORMAT do in MySQL?

DATE_FORMAT(date, format) converts a date or datetime into a formatted string using specifiers like %Y for year, %m for month, and %d for day. For example DATE_FORMAT(NOW(), '%Y-%m-%d') returns a string like 2026-07-18. Use it for display output, not for storing dates.

Should I store dates as formatted strings in MySQL?

No. Store dates in DATE or DATETIME columns and use DATE_FORMAT only when displaying them. A date stored as a string sorts alphabetically, cannot be compared chronologically, and breaks date math. Store the real type, format at the point of display.

What is the difference between %m and %M in DATE_FORMAT?

%m is the numeric month zero-padded (07), while %M is the full month name (July). Case matters throughout DATE_FORMAT - %d is the day number and %D is the day with an ordinal suffix. Mixing up the case is a common mistake.

How do I convert a string to a date in MySQL?

Use STR_TO_DATE(string, format), the inverse of DATE_FORMAT. The format string describes the layout of the input, so STR_TO_DATE('18/07/2026', '%d/%m/%Y') parses that text into a real DATE you can store and compare.

How do I group MySQL rows by month?

Use DATE_FORMAT(created_at, '%Y-%m') in a GROUP BY. It buckets rows by year and month, and because the year comes first the string sorts chronologically, so your monthly report stays in date order without extra work.