FORMAT / TO_CHARFunctions
Buy Me A Coffee

Formatting a date, number, or other value as a string according to a format pattern.

PostgreSQL and Oracle function that converts dates, numbers, and timestamps to formatted strings. Supports a wide range of format patterns for locale-aware output. The most flexible formatting function across databases.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 DATE_FORMAT() for date/datetime values; FORMAT() for numbers (adds thousands separator). No unified TO_CHAR equivalent.
PostgreSQL ✓ Supported 7.4 TO_CHAR() handles dates, timestamps, numbers, and intervals with a rich pattern language. TO_DATE() and TO_NUMBER() are the inverse functions.
SQL Server ✓ Supported 2012 FORMAT() added in 2012 using .NET format strings. CONVERT() with a style code is the older approach and works on all versions. FORMAT() is convenient but notably slower than CONVERT() at scale.
Oracle ✓ Supported 7 TO_CHAR() is the standard for both dates and numbers. NLS session parameters can affect output; use explicit format masks to ensure consistency.
SQLite ✓ Supported 3.0.0 strftime() handles date formatting. For numeric formatting, use printf() / format(), available since 3.38.0.

Details

Date and number formatting is one of the most fragmented areas across SQL engines. PostgreSQL and Oracle share TO_CHAR() with similar (but not identical) pattern languages — Oracle's format tokens are case-sensitive in some contexts; PostgreSQL is case-insensitive. SQL Server's FORMAT() uses .NET composite format strings, which are powerful but measurably slower than CONVERT() for high-volume queries. MySQL splits the concern: DATE_FORMAT() for dates, FORMAT() for numbers. SQLite uses Unix-style strftime() for dates and printf() for numbers. For cross-engine portability, CAST(value AS VARCHAR/TEXT) plus application-layer formatting is often simpler than maintaining five different format strings.

Standard Syntax

-- PostgreSQL / Oracle SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders; -- SQL Server SELECT FORMAT(order_date, 'yyyy-MM-dd') FROM orders;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 7.4 SQL Server: Since 2012 Oracle: Since 7 SQLite: Since 3.0.0

Per-Database Syntax & Notes

MySQL

DATE_FORMAT() for date/datetime values; FORMAT() for numbers (adds thousands separator). No unified TO_CHAR equivalent.

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders; SELECT FORMAT(1234567.89, 2); -- '1,234,567.89'

PostgreSQL

TO_CHAR() handles dates, timestamps, numbers, and intervals with a rich pattern language. TO_DATE() and TO_NUMBER() are the inverse functions.

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders; SELECT TO_CHAR(1234567.89, 'FM999,999,990.00');

SQL Server

FORMAT() added in 2012 using .NET format strings. CONVERT() with a style code is the older approach and works on all versions. FORMAT() is convenient but notably slower than CONVERT() at scale.

SELECT FORMAT(order_date, 'yyyy-MM-dd') FROM orders; SELECT FORMAT(1234567.89, 'N2'); -- '1,234,567.89' -- Older CONVERT style: SELECT CONVERT(VARCHAR, order_date, 23); -- 'yyyy-mm-dd'

Oracle

TO_CHAR() is the standard for both dates and numbers. NLS session parameters can affect output; use explicit format masks to ensure consistency.

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders; SELECT TO_CHAR(1234567.89, '999,999,990.00') FROM dual;

SQLite

strftime() handles date formatting. For numeric formatting, use printf() / format(), available since 3.38.0.

SELECT strftime('%Y-%m-%d', order_date) FROM orders; SELECT printf('%.2f', 1234.5); -- '1234.50'