Eric Campbell 5 min read MySQL

How to Use DATE_FORMAT in MySQL

MySQL's DATE_FORMAT function transforms date and datetime values into formatted strings. While modern applications often handle formatting in the presentation layer, DATE_FORMAT remains crucial for...

Key Insights

  • DATE_FORMAT is essential for converting MySQL dates into human-readable strings, but using it in WHERE clauses destroys index performance—always filter on raw dates first, then format in SELECT
  • The function uses percent-based specifiers like %Y for year and %m for month, which can be combined freely to create any date format your application needs, from ISO 8601 to localized formats
  • NULL dates will return NULL from DATE_FORMAT, so wrap calls in COALESCE() for user-facing queries, and remember that formatting belongs in the database only when you need grouped aggregations or can’t format client-side

Understanding DATE_FORMAT Fundamentals

MySQL’s DATE_FORMAT function transforms date and datetime values into formatted strings. While modern applications often handle formatting in the presentation layer, DATE_FORMAT remains crucial for reports, grouped aggregations, and scenarios where formatting at the database level reduces application complexity.

The function accepts any valid date, datetime, or timestamp value and returns a string based on your format specification. You’ll use this when generating human-readable dates for reports, creating custom date displays that don’t match your application’s default format, or grouping records by formatted date periods.

Basic Syntax and Parameters

DATE_FORMAT takes exactly two parameters:

DATE_FORMAT(date, format)

The first parameter is the date value—this can be a column, a function like NOW(), or a date literal. The second parameter is a format string containing specifiers that tell MySQL how to structure the output.

Here’s the simplest example:

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;
-- Output: 2024-01-15

This converts the current timestamp to an ISO 8601 date string. The format string '%Y-%m-%d' contains three specifiers: %Y (four-digit year), %m (two-digit month), and %d (two-digit day), separated by hyphens.

You can include any literal text in the format string. Only sequences starting with % are interpreted as specifiers:

SELECT DATE_FORMAT(NOW(), 'Today is %W, %M %e, %Y') AS readable_date;
-- Output: Today is Monday, January 15, 2024

Essential Format Specifiers

MySQL provides dozens of format specifiers. Here are the ones you’ll actually use:

Year specifiers:

  • %Y - Four-digit year (2024)
  • %y - Two-digit year (24)

Month specifiers:

  • %m - Month as number with leading zero (01-12)
  • %c - Month as number without leading zero (1-12)
  • %M - Full month name (January)
  • %b - Abbreviated month name (Jan)

Day specifiers:

  • %d - Day with leading zero (01-31)
  • %e - Day without leading zero (1-31)
  • %D - Day with suffix (1st, 2nd, 3rd)
  • %W - Full weekday name (Monday)
  • %a - Abbreviated weekday name (Mon)

Time specifiers:

  • %H - Hour in 24-hour format (00-23)
  • %h or %I - Hour in 12-hour format (01-12)
  • %i - Minutes (00-59)
  • %s - Seconds (00-59)
  • %p - AM or PM

Here’s a comparison of common formats:

SELECT 
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_format,
    DATE_FORMAT(order_date, '%m/%d/%Y') AS us_format,
    DATE_FORMAT(order_date, '%d/%m/%Y') AS european_format,
    DATE_FORMAT(order_date, '%W, %M %D, %Y') AS long_format,
    DATE_FORMAT(order_date, '%h:%i %p') AS time_12hr,
    DATE_FORMAT(order_date, '%H:%i:%s') AS time_24hr
FROM orders
LIMIT 1;

Output example:

iso_format: 2024-01-15
us_format: 01/15/2024
european_format: 15/01/2024
long_format: Monday, January 15th, 2024
time_12hr: 02:30 PM
time_24hr: 14:30:45

Real-World Applications

E-commerce Order Display

When building order history pages, you need readable dates without time components:

SELECT 
    order_id,
    DATE_FORMAT(order_date, '%b %e, %Y') AS order_date_display,
    total_amount,
    status
FROM orders
WHERE customer_id = 12345
ORDER BY order_date DESC;

This produces clean output like “Jan 15, 2024” instead of “2024-01-15 14:30:45”.

Monthly Sales Reports with GROUP BY

This is where DATE_FORMAT truly shines—grouping by formatted dates:

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

Output:

month      | order_count | revenue
2024-03    | 1523        | 45670.00
2024-02    | 1401        | 42130.50
2024-01    | 1678        | 51240.25

Combining with WHERE Clauses (The Right Way)

Never put DATE_FORMAT in your WHERE clause if you’re filtering by date ranges:

-- WRONG: This kills performance
SELECT * FROM orders
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2024-01';

-- RIGHT: Filter first, format in SELECT
SELECT 
    order_id,
    DATE_FORMAT(order_date, '%M %e, %Y at %h:%i %p') AS formatted_date
FROM orders
WHERE order_date >= '2024-01-01' 
  AND order_date < '2024-02-01';

The wrong version prevents MySQL from using indexes on order_date because it must format every row before filtering. The right version uses the index for fast filtering, then formats only the matching rows.

Performance Considerations

DATE_FORMAT is computationally cheap, but using it incorrectly creates expensive queries. The cardinal rule: never use DATE_FORMAT on indexed columns in WHERE clauses.

Performance comparison:

-- Slow: Full table scan
EXPLAIN SELECT * FROM orders
WHERE DATE_FORMAT(order_date, '%Y') = '2024';

-- Fast: Index range scan
EXPLAIN SELECT * FROM orders
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

The first query shows “Using where” with a full table scan. The second uses “range” access with your index.

When to format in the database vs application:

Format in the database when:

  • You’re grouping by formatted dates (monthly reports, daily summaries)
  • The query results feed directly into a CSV export or report
  • You need consistent formatting across multiple applications

Format in the application when:

  • Building user interfaces with JavaScript frameworks
  • Supporting multiple locales (use application i18n)
  • The date format might change based on user preferences

Modern applications typically format dates client-side using libraries like date-fns or Luxon, keeping the database layer focused on data retrieval.

Common Pitfalls and Solutions

NULL Handling

DATE_FORMAT returns NULL when given a NULL date. For user-facing queries, handle this explicitly:

SELECT 
    order_id,
    COALESCE(
        DATE_FORMAT(shipped_date, '%b %e, %Y'),
        'Not yet shipped'
    ) AS ship_date_display
FROM orders;

Alternatively, use IFNULL:

SELECT 
    order_id,
    IFNULL(DATE_FORMAT(shipped_date, '%m/%d/%Y'), 'Pending') AS ship_date
FROM orders;

Locale Considerations

DATE_FORMAT uses the server’s locale for month and day names. If your MySQL server is configured for English but you need Spanish month names, you have two options:

  1. Change the server locale (affects all queries)
  2. Format dates in your application layer with proper i18n

For international applications, formatting client-side is almost always the right choice.

DATE_FORMAT vs STR_TO_DATE

Don’t confuse these functions:

  • DATE_FORMAT: date → string
  • STR_TO_DATE: string → date
-- Converting string to date for comparison
SELECT * FROM orders
WHERE order_date >= STR_TO_DATE('01/15/2024', '%m/%d/%Y');

-- Converting date to string for display
SELECT DATE_FORMAT(order_date, '%m/%d/%Y') FROM orders;

Time Zone Awareness

DATE_FORMAT formats dates in the session’s time zone. If you’re storing UTC timestamps but need local time display:

SELECT 
    DATE_FORMAT(CONVERT_TZ(order_date, '+00:00', '-05:00'), '%Y-%m-%d %h:%i %p') AS eastern_time
FROM orders;

However, time zone conversion is another operation better handled in the application layer where you have access to user preferences and proper time zone libraries.

Practical Recommendations

Use DATE_FORMAT strategically. It’s perfect for GROUP BY aggregations and quick reporting queries. For application UIs, prefer formatting in your application code where you have better control over localization and user preferences.

Always filter on raw date columns to leverage indexes, then format the results. Write your queries to separate filtering logic from presentation logic—your database performance will thank you.

When building reports that humans will read directly (CSV exports, email reports, database-driven dashboards), DATE_FORMAT saves you from post-processing data in application code. When building APIs that serve frontend applications, return ISO 8601 strings and let the client handle formatting.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.