TO_CHAR

Format a date, timestamp, interval, or numeric value as a string using template patterns.

Category: datetimeReturns: STRINGDialect: PostgreSql

Syntax

TO_CHAR(value, fmt)

Description

## Overview Formats a temporal or numeric value as a VARCHAR using template-pattern tokens. For temporal values TO_CHAR uses an uppercase-token template language (YYYY, MM, DD, HH24, MI, SS) rather than the percent-prefixed style used by DATE_FORMAT. Use TO_CHAR when you need locale-aware month and weekday names, explicit fill-mode control, or ISO-week formatting with embedded literals. ## Behavior - Returns a VARCHAR. - Returns NULL if either argument is NULL. - Double-quoted substrings in the pattern are emitted literally (for example "W" becomes W). - FM (fill mode) prefix trims trailing spaces from fixed-width tokens; applying FM to Month produces 'March' rather than 'March '. - Case of tokens influences case of output for Day, Month, and similar names: 'Month' produces 'March', 'MONTH' produces 'MARCH', 'month' produces 'march'. - Time tokens applied to DATE-only inputs produce zero-valued fields. ## Format specifiers - **YYYY**: four-digit year. - **YY**: two-digit year. - **IYYY**: four-digit ISO week-year. - **MM**: two-digit month. - **Month / Mon**: full or abbreviated month name (case follows pattern). - **DD**: two-digit day-of-month. - **DDD**: day-of-year. - **Day / Dy**: full or abbreviated weekday name. - **D**: day-of-week (1-7). - **IW**: ISO week number. - **Q**: calendar quarter. - **HH24**: two-digit hour 00-23. - **HH** or **HH12**: hour 01-12. - **AM / PM / am / pm**: meridian. - **MI**: two-digit minute. - **SS**: two-digit second. - **MS**: milliseconds (three digits). - **US**: microseconds (six digits). - **TZ / TZH / TZM**: time zone name, hour offset, minute offset. - **FM**: fill-mode prefix (no padding for the next token). - **"literal"**: emit text literally. ## Compatibility - Template-pattern family widely used across analytical SQL engines. For percent-style strftime patterns use DATE_FORMAT.

Parameters

Name	Type	Description
`value`		Specifies the value to format. The applicable tokens depend on whether value is temporal or numeric.
`fmt`		Specifies the format pattern. For datetime values common tokens include YYYY, MM, DD, HH24, MI, SS, FM (fill mode), and quoted literals in double quotes.

Examples

-- Basic date formatting
SELECT TO_CHAR(TIMESTAMP '2025-03-15 14:30:00', 'YYYY-MM-DD') AS d;

-- Full month name with fill-mode trimming
SELECT TO_CHAR(DATE '2025-07-04', 'FMMonth DD, YYYY') AS label;

-- Time portion only (24-hour clock)
SELECT TO_CHAR(TIMESTAMP '2025-03-15 14:30:00', 'HH24:MI:SS') AS hms;

-- Weekday name
SELECT TO_CHAR(DATE '2025-03-15', 'FMDay') AS weekday;

-- ISO week and year with a literal 'W' in the output
SELECT TO_CHAR(DATE '2025-01-06', 'IYYY-"W"IW') AS iso_key;

-- Format an interval as days:hours:minutes
SELECT TO_CHAR(INTERVAL '1 day 2 hours 30 minutes', 'DD:HH24:MI') AS duration;

Pitfalls

HH12 pairs with AM/PM; HH24 produces a 24-hour clock. Emitting HH12 without an AM/PM token loses morning/afternoon information silently.
MI is minutes; MM is months. Using MM where MI was intended silently emits the month in the minute slot.
Token case affects name tokens: 'Month' produces 'March', 'MONTH' produces 'MARCH'. Literal strings inside double quotes are emitted verbatim regardless of surrounding token case.
TO_CHAR applied to an INTERVAL uses the DD/HH24/MI tokens against the normalized components; it is not a formatted total-duration output. For total seconds use EXTRACT(EPOCH FROM interval).
Templates that omit FM pad short values with spaces or zeros as appropriate. When concatenating formatted pieces for a key, prefer FM tokens (for example FMMonth) to avoid stray whitespace.