MySQL DATE_FORMAT() → PostgreSQL TO_CHAR()

Quick answer

to_char(expr, format) is PostgreSQL's equivalent of DATE_FORMAT(expr, format), but the format string itself uses a completely different token vocabulary (YYYY/MM/DD instead of %Y/%m/%d) — every format string needs re-translating, not just the function name.

Why it breaks

MySQL's DATE_FORMAT() format specifiers are %-prefixed (%Y, %m, %d, %H, %i, %s). PostgreSQL's to_char() format tokens are bare words (YYYY, MM, DD, HH24, MI, SS) with no % prefix at all — renaming just the function call while keeping the MySQL-style format string produces either an error or, worse, a string that to_char() interprets as a mix of literal characters and unintended tokens.

Real examples

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

SELECT to_char(created_at, 'YYYY-MM-DD') FROM orders;

SELECT DATE_FORMAT(created_at, '%h:%i %p') FROM orders;

SELECT to_char(created_at, 'HH12:MI AM') FROM orders;

Safe migration options

• Translate common tokens directly: %Y → YYYY, %y → YY, %m → MM, %d → DD, %H → HH24, %h → HH12, %i → MI, %s → SS, %p → AM/PM.
• Wrap literal characters (anything not meant as a token) in double quotes inside the to_char() format string — PostgreSQL treats unquoted letters as format tokens even if MySQL treated them as literals.
• Double-check month/day name tokens specifically: %M (full month name) → Month, %a (abbreviated weekday) → Dy — these are easy to mis-map since the letter casing carries meaning in to_char (Month vs MONTH vs month all format differently).

PostgreSQL solution

to_char(timestamp_expr, 'YYYY-MM-DD') replaces DATE_FORMAT(datetime_expr, '%Y-%m-%d')

Validation

References

• MySQL Manual — Date and Time Functions (v8.0) — checked 2026-07-30
• PostgreSQL Manual — Data Type Formatting Functions (v17) — checked 2026-07-30

Last verified 2026-07-30.