MySQL DATE_FORMAT() Function

Syntax

DATE_FORMAT(date, format)

DATE_FORMAT returns a formatted string from a date or datetime value.

Basic Example

SELECT DATE_FORMAT('2026-05-17', '%Y-%m-%d') AS iso_date;
    -- Result: 2026-05-17

Practical query example

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month_bucket,
       COUNT(*) AS orders_count
    FROM orders
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
    ORDER BY month_bucket;

Useful format tokens

%Y = 4-digit year
    %m = 2-digit month
    %d = 2-digit day
    %H = hour (00-23)
    %i = minutes (00-59)
    %s = seconds (00-59)

When to use it

Use DATE_FORMAT for dashboards, export labels, monthly grouping keys, and user-facing date strings in MySQL queries.

Common mistakes

• Using YYYY-MM-DD style tokens directly. MySQL expects percent tokens like %Y-%m-%d.
• Assuming DATE_FORMAT returns a date type. It returns a formatted string.
• Using DATE_FORMAT in filters where raw date comparison is faster and more index-friendly.

Cross-dialect alternatives

• PostgreSQL: TO_CHAR(date_col, 'YYYY-MM-DD')
• SQL Server: FORMAT(date_col, 'yyyy-MM-dd') or CONVERT styles
• Oracle: TO_CHAR(date_col, 'YYYY-MM-DD')

Troubleshoot MySQL DATE_FORMAT problems

Related SQL Functions

• MySQL STR_TO_DATE() Function
• MySQL DATE_ADD() Function
• MySQL DATE_SUB() Function
• MySQL DATEDIFF() Function
• MySQL NOW() Function
• MySQL CURDATE() Function

FAQ

Why does YYYY-MM-DD not work as expected?

MySQL DATE_FORMAT uses percent-style tokens, so use %Y-%m-%d instead.

Does DATE_FORMAT return DATE or VARCHAR?

It returns a formatted string value, not a native DATE type.

Can DATE_FORMAT affect query performance?

Yes. Wrapping indexed columns in functions can reduce index usage in WHERE clauses.