DATE_FORMAT

Format a date or timestamp as a string using percent-prefixed pattern tokens.

Category: datetimeReturns: STRINGDialect: Standard

Syntax

DATE_FORMAT(date, fmt)

Description

## Overview Formats a DATE or TIMESTAMP value as a VARCHAR string using percent-prefixed tokens. This is the traditional strftime-style formatting convention. For the alternative Java SimpleDateFormat style (yyyy-MM-dd), use TO_CHAR. Use DATE_FORMAT for building display strings, deterministic bucket keys (yyyy-MM month keys are a common ETL pattern), and conversion of temporal values into string form for export. ## Behavior - Returns a VARCHAR. - Returns NULL if either argument is NULL. - Unknown tokens are emitted as literal characters (the leading % is consumed). - A literal % in the output is produced by doubling it: '%%'. - Time tokens applied to a DATE-only input produce zero-valued fields (for example %H returns '00'). ## Format specifiers - **%Y**: four-digit year (2025). - **%y**: two-digit year (25). - **%m**: two-digit month (01-12). - **%c**: month (1-12, no padding). - **%M**: full month name (January). - **%b**: abbreviated month name (Jan). - **%d**: two-digit day-of-month (01-31). - **%e**: day-of-month (1-31, no padding). - **%j**: day-of-year (001-366). - **%W**: full weekday name (Monday). - **%a**: abbreviated weekday name (Mon). - **%w**: day-of-week number (0 = Sunday). - **%H**: two-digit hour 00-23 (24-hour). - **%k**: hour 0-23 (no padding). - **%h** or **%I**: two-digit hour 01-12 (12-hour). - **%p**: AM or PM. - **%i**: two-digit minute 00-59. - **%s**: two-digit second 00-59. - **%f**: microseconds 000000-999999. - **%T**: time in HH:MI:SS form. - **%r**: time in hh:MI:SS AM/PM form. - **%%**: literal percent sign. ## Compatibility - Percent-style tokens align with the strftime family and common SQL DATE_FORMAT conventions. For Java SimpleDateFormat style, use TO_CHAR with patterns such as 'YYYY-MM-DD' or 'yyyy-MM-dd'.

Parameters

Name	Type	Description
`date`		Specifies the date or timestamp value to format.
`fmt`		Specifies the format pattern using percent-prefixed tokens. Common tokens include %Y (4-digit year), %m (month 01-12), %d (day 01-31), %H (hour 00-23), %i (minute 00-59), %s (second 00-59).

Examples

-- yyyy-MM-dd format from a DATE
SELECT DATE_FORMAT(DATE '2025-03-15', '%Y-%m-%d') AS d;

-- Full timestamp format
SELECT DATE_FORMAT(TIMESTAMP '2025-03-15 14:30:00', '%Y/%m/%d %H:%i:%s') AS ts;

-- Month and year only
SELECT DATE_FORMAT(DATE '2025-07-04', '%m-%Y') AS month_year;

-- Human-readable: 'Thursday, December 25, 2025'
SELECT DATE_FORMAT(DATE '2025-12-25', '%W, %M %d, %Y') AS label;

-- Build a date-bucketed report
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_key, COUNT(*) AS orders
FROM commerce.sales.orders
GROUP BY month_key
ORDER BY month_key;

-- Extract time-only portion
SELECT DATE_FORMAT(event_ts, '%H:%i') AS time_hhmm
FROM telemetry.web.requests
LIMIT 10;

Pitfalls

DATE_FORMAT uses percent-prefixed tokens (%Y-%m-%d), not the Java SimpleDateFormat tokens used by TO_CHAR (yyyy-MM-dd). Mixing the two patterns silently produces garbage output.
%m is month (01-12) and %i is minute (00-59). Confusing %m with %M (full month name) or %i with %M is a common source of bugs. Note that %M is also used for minutes in some strftime variants; in this engine %M is the full month name.
Formatting a DATE value with time tokens returns zero-valued fields. DATE_FORMAT(DATE '2025-03-15', '%H:%i') returns '00:00'.
%y (two-digit year) loses century information. Never use %y as a key that must round-trip through TO_DATE without also specifying a pivot-year policy.
Unknown tokens lose their leading percent but retain the following character literally. Typos like %Q produce a literal 'Q' in the output rather than an error.