Explain TO_CHAR, TO_DATE and TO_NUMBER conversion functions in PL/SQL

How it works

TO_CHAR, TO_DATE and TO_NUMBER are Oracle's explicit conversion functions between strings, dates and numbers, each taking an optional format model as the second argument (e.g. 'DD-MON-YYYY', '999,999.00'). Relying on implicit conversion instead — comparing a DATE column to a bare string literal, say — is a common source of bugs and unexpectedly bad execution plans; converting explicitly with a format model that matches the actual data is the safer habit.

SELECT
  TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI')  AS formatted_date,
  TO_DATE('24-08-2026', 'DD-MM-YYYY')       AS parsed_date,
  TO_NUMBER('1,234.50', '9,999.99')         AS parsed_number,
  TO_CHAR(1234.5, '$9,999.00')              AS formatted_currency
FROM dual;

Related Questions