ToChar Function Explained: Syntax, Examples, and Use Cases

SQL ToChar Tips — Formatting Dates and Numbers EfficientlyFormatting dates and numbers consistently is a small task that pays large dividends: readable reports, fewer bugs, and code that communicates intent. The TO_CHAR function (available in many SQL dialects such as Oracle, PostgreSQL, and others) is a powerful, flexible tool for converting dates, timestamps, and numeric values to formatted strings. This article covers practical tips, common patterns, performance considerations, and portability notes to help you use TO_CHAR efficiently and correctly.

---

What TO_CHAR does (quick overview)

TO_CHAR converts dates, timestamps, and numbers into formatted text. You pass the value and a format model (pattern) that describes how the output should look. For dates/timestamps, the model uses format elements like YYYY, MM, DD, HH24, MI, SS, etc. For numbers, it uses elements such as 9, 0, ., ,, $, and MI (for negative sign).

---

Common use cases

• Presenting dates in a readable or locale-specific format (e.g., “2025-09-02”, “02 Sep 2025”, “September 2, 2025”).
• Producing fixed-width numeric strings for reports, padding with zeros.
• Formatting currency values with thousands separators and two decimals.
• Extracting parts of a date/time (year, month name, quarter) as text for grouping or labeling.
• Creating human-readable timestamps for logs.

---

Date and timestamp formatting tips

1. Use ISO formats for data interchange

• When generating dates for APIs or machine-to-machine communication, prefer ISO 8601-like formats: YYYY-MM-DD or YYYY-MM-DD”T”HH24:MI:SS. Example:
  • Oracle/Postgres: TO_CHAR(some_date, ‘YYYY-MM-DD’)
  • For full timestamp: TO_CHAR(some_ts, ‘YYYY-MM-DD”T”HH24:MI:SS’)

1. Always specify 24-hour time when clarity matters

• Use HH24 to avoid ambiguity. Example: TO_CHAR(ts, ‘HH24:MI:SS’)

1. Use textual month/day names when readability matters

• Format elements like MON, MONTH, Dy, and Day produce abbreviated or full names:
  • TO_CHAR(date_col, ‘DD Mon YYYY’) -> 02 Sep 2025
  • TO_CHAR(date_col, ‘FMMonth DD, YYYY’) -> September 2, 2025
• FM (fill mode) suppresses padding spaces.

1. Be careful with case and punctuation in format models

• Some systems are case-insensitive for format elements, but using the canonical case (uppercase) improves readability.
• Literal text must be quoted or escaped. Example: TO_CHAR(ts, ‘YYYY “year”’) or using double quotes for literals in Oracle.

1. Locales and NLS settings

• Month and day names, AM/PM markers, and date order can be influenced by session NLS settings (Oracle) or the locale in the database. If you need a fixed language, use explicit NLS parameters where available:
  • Oracle: TO_CHAR(date_col, ‘MON’, ‘NLS_DATE_LANGUAGE=AMERICAN’)

1. Use FM to trim unwanted padding

• Format elements for months and day names often add spaces to reach a fixed width. Prefix your format with FM to remove leading/trailing spaces: TO_CHAR(date_col, ‘FMDd Mon YYYY’)

1. Be explicit about fractional seconds

• For timestamps with sub-second precision, use FF1..FF9 (Oracle) to specify fractional second digits: TO_CHAR(ts, ‘HH24:MI:SS.FF3’)

---

Number formatting tips

1. Choose 9 vs 0 carefully

• 9 displays a digit or space for leading zeros; 0 forces a zero. Example:
  • TO_CHAR(42, ‘999’) -> ‘ 42’
  • TO_CHAR(42, ‘000’) -> ‘042’

1. Use FM to remove leading spaces

• TO_CHAR(42, ‘FM999’) -> ‘42’

1. Thousands separators and decimals

• Use ‘,’ for grouping and ‘.’ for decimal in format models:
  • TO_CHAR(12345.67, ‘9,999.99’) -> ‘12,345.67’
• For currency, include a currency symbol in the format (and handle negative values):
  • TO_CHAR(-1234.5, ‘L9,999.99MI’) where L is currency symbol; MI puts minus at end.

1. Rounding behavior

• TO_CHAR applies rounding to match the number of decimal places in your format. Be aware this can change values in reports; use TRUNC if you need to cut instead of round.

1. Beware of localization

• Decimal and thousands separators depend on NLS/locale. If consistent symbols are required, set NLS_NUMERIC_CHARACTERS explicitly where supported.

---

Performance and usage patterns

1. Avoid formatting in WHERE/JOIN/ORDER BY when possible

• Wrapping indexed date or numeric columns in TO_CHAR prevents index use and can cause full table scans. Instead:
  • Convert literals to the column type: WHERE date_col = DATE ‘2025-09-02’ or WHERE date_col >= TIMESTAMP ‘2025-09-01 00:00:00’ AND date_col < TIMESTAMP ‘2025-09-02 00:00:00’
  • For numbers, convert the literal: WHERE numeric_col = TO_NUMBER(‘123’)

1. Use computed/persisted columns or indexes for frequent formatted queries

• If you frequently query against a particular string format, create a persisted/generated column and index it.

1. Formatting at the presentation layer

• Keep formatting in the application or reporting layer when possible. Databases should store native types; formatting in SQL is for final output.

1. Batch-formatting vs on-the-fly

• For large exports, generate formatted fields in a controlled batch process to avoid repetitive format calls in high-concurrency OLTP queries.

---

Portability: differences between SQL dialects

• Oracle: TO_CHAR supports many format elements (FF, NLS parameters, ‘RR’ year handling). Formatting tokens like FM, L, MI are supported.
• PostgreSQL: TO_CHAR exists and is similar for numbers/dates but some tokens differ slightly; FM is supported, and template patterns like ‘YYYY’ work. Fractional seconds use ‘MS’ or ‘US’ patterns via date_part for microseconds in some cases.
• Redshift/SQL Server/MySQL: SQL Server and MySQL do not use TO_CHAR; they have CONVERT/FORMAT/DATE_FORMAT equivalents. When writing cross-platform SQL, avoid TO_CHAR or wrap it in compatibility layers.

---

Examples

Date examples:

• ISO date: TO_CHAR(order_date, ‘YYYY-MM-DD’)
• Human-friendly: TO_CHAR(order_date, ‘FMMonth DD, YYYY’)
• Timestamp with millis: TO_CHAR(created_at, ‘YYYY-MM-DD”T”HH24:MI:SS.FF3’)

Number examples:

• Currency: TO_CHAR(amount, ‘L9,999,990.00’)
• Zero-padded ID: TO_CHAR(id, ‘000000’) — produces ‘000123’ for id=123
• Percentage: TO_CHAR(rate * 100, ‘FM99990.00’) || ‘%’

---

Common pitfalls and how to avoid them

• Using TO_CHAR in joins/wheres (prevents index use). Fix by converting literals or indexing a formatted column.
• Relying on session NLS settings for consistent output. Fix by specifying NLS parameters or setting the session locale explicitly.
• Unexpected rounding. Fix by using TRUNC when truncation is desired.
• Assuming same format tokens across databases. Fix by checking target DB docs or using application-side formatting.

---

Quick reference: useful format elements (Oracle/Postgres)

• Dates: YYYY, YY, MM, MON, MONTH, DD, DDD, DY, Day, HH24, HH12, MI, SS, FF1..FF9
• Numbers: 9, 0, FM, ., ,, L, MI, PR

---

Closing notes

TO_CHAR is indispensable for creating readable outputs directly in SQL, but use it judiciously to avoid performance and portability issues. Favor native types in storage and apply TO_CHAR at the edge where you produce final output — reports, exports, and user interfaces.