SQL TO_CHAR: Formatting Dates and Numbers into Strings with Templates

TO_CHAR takes a date, a timestamp or a number and returns a string assembled from a text template. It is PostgreSQL's main tool for human-facing output: a fixed report date, a time without seconds, a price with thousands separators.

Templates for dates and times

The first argument is the value, the second is a template string of field codes. Punctuation and literal characters in the template are kept as written, so you place the dashes, colons and spaces yourself.

SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI') AS ts
FROM users
ORDER BY created_at
LIMIT 5;
-- 2026-06-17 14:32

The codes you reach for most:

• YYYY is the 4-digit year, YY the 2-digit one.
• MM is the month number (01-12), DD the day, HH24 the 24-hour hour, MI minutes, SS seconds.
• HH12 with AM/PM gives 12-hour time.
• Day, Mon, Month are the textual day and month names (case rules below).

Mind the classic gotcha: MM is the month, MI is minutes. It is very easy to write HH24:MM and get the month number where you wanted minutes.

Month names and localization

The Day, Month, Mon, Dy codes copy the case of the template: Day yields Monday, DAY yields MONDAY, day yields monday. Text fields are space-padded to a fixed width; strip that padding with the FM modifier.

SELECT
  TO_CHAR(created_at, 'FMDay, FMDD FMMonth YYYY') AS human_date,
  TO_CHAR(created_at, 'Dy')                       AS short_dow
FROM orders
LIMIT 3;
-- Wednesday, 17 June 2026 | Wed

The language of the names comes from the lc_time setting. To force localized names in a single query, switch on translate mode with the TM prefix:

SELECT TO_CHAR(
  created_at,
  'TMDay, DD TMMonth YYYY'
) AS local_date
FROM orders;
-- with lc_time = de_DE.UTF-8: Mittwoch, 17 Juni 2026

The TM (translate mode) prefix turns on locale-aware names. Without it you always get the English Monday/June.

Formatting numbers

With numbers, TO_CHAR controls width, grouping and sign through a mask built from 9, 0, D, G and friends.

SELECT
  TO_CHAR(amount, 'FM999G999G990D00')  AS money,
  TO_CHAR(id,     '0000')              AS padded_id
FROM orders
LIMIT 3;
-- 1,250.00 | 0042

How to read the mask:

• 9 is a digit with leading zeros suppressed; 0 is a digit that is always printed (use it to pad on the left).
• D is the decimal point, G the group separator; the actual characters follow the locale (lc_numeric).
• FM again trims the leading blank that TO_CHAR reserves for the sign.
• L inserts the currency symbol, S prints the +/- sign.

Gotcha: without FM, positive numbers come out with a leading space where the minus would go, and your report columns drift. For money you almost always want FM plus an explicit 0 before D, otherwise 0.50 renders as .50.

How PostgreSQL differs from MySQL

MySQL has no TO_CHAR for dates; it uses DATE_FORMAT with a completely different %-based code set.

-- PostgreSQL
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI') FROM users;

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') FROM users;

The key mismatches:

• Minutes: PostgreSQL uses MI, MySQL uses %i (while %m in MySQL is the month).
• 24-hour clock: PostgreSQL HH24, MySQL %H.
• Month name: PostgreSQL Month/Mon, MySQL %M/%b.
• Numbers: in MySQL you reach for a separate FORMAT(amount, 2) for grouping, not TO_CHAR.

ClickHouse takes a third path: formatDateTime(created_at, '%Y-%m-%d %H:%M'), MySQL-like codes, but here %M is minutes, not the month. The lesson: a TO_CHAR template is not portable, and a database switch means rewriting it from scratch.