Format a Date as a String in SQL (per Dialect)

Render a date as text for reports, labels, or grouping keys.

As YYYY-MM-DD

-- MySQL
SELECT DATE_FORMAT(d, '%Y-%m-%d');

-- PostgreSQL
SELECT TO_CHAR(d, 'YYYY-MM-DD');

-- BigQuery
SELECT FORMAT_DATE('%Y-%m-%d', d);

-- Snowflake
SELECT TO_CHAR(d, 'YYYY-MM-DD');

-- SQLite
SELECT strftime('%Y-%m-%d', d);

-- DuckDB
SELECT strftime(d, '%Y-%m-%d');

As ‘Jan 2024’ (month + year)

SELECT DATE_FORMAT(d, '%b %Y');         -- MySQL
SELECT TO_CHAR(d, 'Mon YYYY');          -- PostgreSQL / Snowflake
SELECT FORMAT_DATE('%b %Y', d);         -- BigQuery
SELECT strftime('%m %Y', d);            -- SQLite (no month name; gives '01 2024')
SELECT strftime(d, '%b %Y');            -- DuckDB

Gotchas: two format-token families again — strftime-style %Y-%m-%d (MySQL, BigQuery, SQLite, DuckDB) vs template patterns YYYY-MM-DD (Postgres, Snowflake). Watch the case: in Postgres/Snowflake MM = month but mm/MI = minutes, and DD = day but DDD = day-of-year. SQLite’s strftime has no month-name token, so ‘Jan’ needs a manual CASE or a lookup. Note the argument order flips too: DuckDB is strftime(date, format), SQLite is strftime(format, date).