MySQL DATE_FORMAT(): Format Date and Time Values

MySQL DATE_FORMAT() converts a DATE, DATETIME or TIMESTAMP value into a formatted string. The first argument is the date value and the second is the format pattern.

SELECT DATE_FORMAT(
    '2026-09-09',
    '%d/%m/%Y'
) AS formatted_date;

Output:

09/09/2026

Some common examples:

SELECT DATE_FORMAT(
    '2026-06-23',
    '%d-%M-%Y'
); -- 23-June-2026

SELECT DATE_FORMAT(
    '2026-05-12',
    '%m/%d/%Y %T'
); -- 05/12/2026 00:00:00

SELECT DATE_FORMAT(
    '2026-07-23 22:54:58',
    '%W %D %M %Y %T'
);

MySQL DATE_FORMAT() Syntax Top ↑

DATE_FORMAT(date_value, format_string)

The format string contains specifiers beginning with %. For example, %Y gives a four-digit year, %m gives a two-digit month and %d gives a two-digit day.

Format a Date Column from a MySQL Table Top ↑

If dt_tb contains a DATETIME column named dt, format it directly in the SELECT list:

SELECT id,
       dt,
       DATE_FORMAT(
           dt,
           '%m/%d/%Y %T'
       ) AS my_date
FROM dt_tb;

The original database value remains unchanged. Only the returned my_date column is formatted.

Common DATE_FORMAT() Examples Top ↑

For machine-readable database work, keep dates stored in native MySQL date types. Use DATE_FORMAT() mainly when you need a specific text representation in query output.

DATE_FORMAT() Format Specifiers Top ↑

Filtering by Month or Year Top ↑

DATE_FORMAT() can be used in a WHERE condition:

SELECT id, dt
FROM dt_tb
WHERE DATE_FORMAT(
    dt,
    '%Y-%m'
) = '2004-05';

However, when you are filtering a real DATE or DATETIME column, comparing the original column to a date range is often a better pattern:

SELECT id, dt
FROM dt_tb
WHERE dt >= '2004-05-01 00:00:00'
  AND dt <  '2004-06-01 00:00:00';

This avoids wrapping the table column in a formatting function and gives MySQL a better opportunity to use an index on dt.

Sorting Formatted Dates Correctly Top ↑

If a formatted date is returned as text, sorting that display string can produce an order different from chronological order.

Prefer:

SELECT id,
       dt,
       DATE_FORMAT(
           dt,
           '%d/%m/%Y'
       ) AS display_date
FROM dt_tb
ORDER BY dt ASC;

The original DATETIME column controls chronology while DATE_FORMAT() controls only the displayed text.

DATE_FORMAT() and NULL Values Top ↑

If the date expression is NULL, DATE_FORMAT() returns NULL:

SELECT DATE_FORMAT(
    NULL,
    '%d/%m/%Y'
);

If the application needs display text for missing dates, handle that separately:

SELECT COALESCE(
    DATE_FORMAT(
        dt,
        '%d/%m/%Y'
    ),
    'Not available'
) AS display_date
FROM dt_tb;

Month and Weekday Names Top ↑

Formats such as %M, %W, %a and %b return language-dependent month or weekday names. MySQL uses the session's lc_time_names setting for these names.

SELECT @@lc_time_names;

For an English-language tutorial, examples such as June and Sunday assume an English time-name locale.

MySQL DATE_FORMAT() vs PHP Date Formatting Top ↑

There are two valid places to format a database date for display:

• MySQL: use DATE_FORMAT() when the query itself should return formatted text.
• PHP: return the native date value and format it in application code when presentation belongs in the PHP layer.

The format symbols are not the same between MySQL and PHP, so do not copy a format pattern from one language directly into the other.

Modern Sample Table for DATE_FORMAT() Top ↑

The original version of this tutorial used zero dates such as '0000-00-00' and the old TYPE=MyISAM syntax. Those examples are no longer a good default for modern MySQL.

A cleaner sample table is:

CREATE TABLE dt_tb (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    dt DATETIME NULL DEFAULT NULL,
    dt2 DATE NULL DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

INSERT INTO dt_tb
    (dt, dt2)
VALUES
    (
        '2004-10-26 00:00:00',
        '2005-01-25'
    ),
    (
        '2004-05-05 23:56:25',
        '2005-06-12'
    ),
    (
        '2005-12-08 13:20:10',
        '2005-06-06'
    );

Use SQL NULL when a date is genuinely unknown instead of inventing an invalid zero-date placeholder.

Common DATE_FORMAT() Mistakes Top ↑

Using DATE_FORMAT() to change the stored value Top ↑

DATE_FORMAT() returns formatted text. It does not rewrite the underlying DATE or DATETIME value.

Filtering an indexed date column only through DATE_FORMAT() Top ↑

For month or date-range searches, direct boundary comparisons are often more index-friendly than applying DATE_FORMAT() to every row.

Sorting by a display string instead of the date column Top ↑

Formats such as dd/mm/yyyy are text. Sort by the native date column when chronological order matters.

Confusing MySQL and PHP format symbols Top ↑

The formatting tokens differ. For example, MySQL uses %i for minutes.

Using zero dates as a missing-value placeholder Top ↑

Modern MySQL configurations can reject invalid zero dates. Use a valid date or NULL according to the data model.

Assuming month and weekday names are always English Top ↑

Named months and weekdays depend on MySQL's lc_time_names setting.

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: