DATE_FORMAT()

Syntax

How it works

DATE_FORMAT() is MySQL's function for rendering a date or datetime as a human readable string. You pass a date and a format pattern such as '%Y-%m-%d', and it returns text like 2026-07-05. It is the display counterpart to functions that pull dates apart, such as EXTRACT().

The idea is portable but the syntax is not. MySQL uses percent tokens (%Y, %m, %d). PostgreSQL and Oracle use TO_CHAR(date, 'YYYY-MM-DD') with letter patterns. SQL Server has no DATE_FORMAT at all: modern versions use FORMAT(date, 'yyyy-MM-dd') with .NET patterns, while older code uses CONVERT(varchar, date, style) with a numeric style code. Getting the same output on each engine means learning three different token vocabularies.

Because the output is always a string, formatting is a presentation step. Keep values in a real DATE or TIMESTAMP type in the database and format only when you show the value, otherwise you lose date arithmetic, correct sorting and range filters.

Examples

Format a date as YYYY-MM-DD (all engines)

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS ymd FROM orders;

-- PostgreSQL / Oracle
SELECT TO_CHAR(created_at, 'YYYY-MM-DD') AS ymd FROM orders;

-- SQL Server
SELECT FORMAT(created_at, 'yyyy-MM-dd') AS ymd FROM orders;

ymd
----------
2026-07-05

Human friendly Mon DD, YYYY

-- MySQL: %b = short month, %d = 2 digit day, %Y = 4 digit year
SELECT DATE_FORMAT(created_at, '%b %d, %Y') AS pretty
FROM orders;

pretty
------------
Jul 05, 2026

Full month name and year

-- MySQL: %M is the full month name
SELECT DATE_FORMAT('2026-07-05', '%M %Y') AS label;   -- July 2026

-- PostgreSQL: 'Month' is padded; use FM to trim blanks
SELECT TO_CHAR(DATE '2026-07-05', 'FMMonth YYYY') AS label;  -- July 2026

label
---------
July 2026

Time with 12 hour clock and AM/PM

-- MySQL: %h = 12 hour, %i = minutes, %p = AM/PM
SELECT DATE_FORMAT(created_at, '%h:%i %p') AS clock
FROM orders;

-- PostgreSQL: HH12:MI AM
SELECT TO_CHAR(created_at, 'HH12:MI AM') AS clock FROM orders;

clock
--------
09:30 PM

SQL Server CONVERT with a numeric style code

-- Style 23 gives yyyy-mm-dd; 101 gives mm/dd/yyyy
SELECT CONVERT(varchar(10), created_at, 23) AS ymd,
       CONVERT(varchar(10), created_at, 101) AS us_date
FROM orders;

ymd        | us_date
-----------+-----------
2026-07-05 | 07/05/2026

Common mistakes

-- Storing a formatted string in the table.
-- Now sorting and range filters break: text sorts
-- lexically, not chronologically.
UPDATE orders
SET order_day = DATE_FORMAT(created_at, '%d/%m/%Y');

-- Keep a real DATE/TIMESTAMP column and format
-- only when you display the value.
SELECT DATE_FORMAT(created_at, '%d/%m/%Y') AS shown
FROM orders
ORDER BY created_at;   -- sort on the real date

-- MySQL %-tokens do NOT work in PostgreSQL or Oracle.
-- This raises an error or returns the pattern literally.
SELECT TO_CHAR(created_at, '%Y-%m-%d') FROM orders;

-- PostgreSQL / Oracle use letter patterns, not %-tokens
SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM orders;

-- SQL Server FORMAT() is convenient but slow in bulk:
-- it calls into the .NET CLR once per row.
SELECT FORMAT(created_at, 'yyyy-MM-dd') AS ymd
FROM orders;   -- millions of rows

-- CONVERT with a style code is far faster at scale.
SELECT CONVERT(varchar(10), created_at, 23) AS ymd
FROM orders;

Performance

Formatting a date wraps the column in a function, so a predicate like WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2026-07-05' is not sargable and cannot use an index on created_at. Filter on the raw date instead, for example WHERE created_at >= '2026-07-05' AND created_at < '2026-07-06', and format only in the SELECT list.

In SQL Server, FORMAT() is convenient but measurably slower than CONVERT() at scale because it invokes the .NET CLR for every row. On large result sets prefer a CONVERT(varchar, date, style) style code, or push formatting to the application tier.

Because the token vocabularies differ across engines, consider formatting in the application or reporting layer for cross database code. Keep the database returning a real date type and let the presentation layer localise it, which also avoids scattering engine specific patterns through your SQL functions.

Interview questions

SELECT DATE_FORMAT('2026-07-05', '%Y-%m-%d') AS ymd;   -- 2026-07-05