5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions

5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions

Explicit datatype conversion in Oracle SQL is performed primarily through three fundamental built-in functions: TO_CHAR, TO_DATE, and TO_NUMBER. These functions allow developers to transform data between Character, Datetime, and Numeric domains with absolute precision, complete NLS independence, and customized formatting.

                                  +----------------+
                                  |                |
                     +----------->|   CHARACTER    |<-----------+
                     |            |  (VARCHAR2)    |            |
                     |            |                |            |
              TO_CHAR(date)       +----------------+      TO_CHAR(number)
              TO_DATE(string)     |                |      TO_NUMBER(string)
                     |            |                |            |
                     |            v                v            |
              +---------------+                        +----------------+
              |     DATE      |                        |     NUMBER     |
              +---------------+                        +----------------+

---

The TO_CHAR Function (Number to Character)

The TO_CHAR(number, [format_model], [nls_parameters]) function converts an internal numeric value into a formatted VARCHAR2 string.

Numeric Format Model Elements

Numeric Format Overflow: The Hash Sign (###)

A critical 1Z0-071 exam concept is numeric format width overflow:

• If a number has more integer digits than the format model accommodates, Oracle does not truncate the number or raise an error; instead, it fills the entire output width with octothorpes / hash symbols (###). The output width is the width of the format model plus one leading position that Oracle reserves for the sign, so TO_CHAR(12345, '999') returns four hash characters, not three.
• If a number has more decimal digits than the format model accommodates, Oracle rounds the decimal portion without returning hash symbols.

-- Integer overflow: 5 integer digits provided, but mask only allocates 3 digits:
SELECT TO_CHAR(12345, '999') FROM dual;     --> '####'

-- Decimal precision: rounded to 2 decimal places:
SELECT TO_CHAR(12.3456, '99.99') FROM dual; --> ' 12.35'

---

The TO_CHAR Function (Date to Character)

The TO_CHAR(date, [format_model], [nls_parameters]) function converts an internal DATE or TIMESTAMP value into a formatted character string.

Date Format Model Elements

+-------------------------------------------------------------------------+
|                         DATE FORMAT ELEMENTS                            |
+-------------------------------------------------------------------------+
| YEAR ELEMENTS:                                                          |
| - YYYY / RRRR : 4-digit year (e.g. 2026)                                |
| - YY / RR     : 2-digit year (e.g. 26)                                  |
| - YEAR        : Year spelled out (e.g. TWENTY TWENTY-SIX)               |
| - SCC / CC    : Century (SCC prefixes BC dates with '-')                |
+-------------------------------------------------------------------------+
| MONTH ELEMENTS:                                                         |
| - MM          : 2-digit numeric month (01 - 12)                         |
| - MON         : 3-letter abbreviated month name (e.g. MAY)              |
| - MONTH       : Full month name, padded with trailing spaces (MAY      )|
| - RM          : Roman numeral month (I - XII)                           |
+-------------------------------------------------------------------------+
| DAY ELEMENTS:                                                           |
| - DD          : Numeric day of the month (01 - 31)                      |
| - DDD         : Numeric day of the year (001 - 366)                     |
| - DAY         : Full day name, padded with trailing spaces (FRIDAY    )|
| - DY          : 3-letter abbreviated day name (e.g. FRI)                |
| - D           : Numeric day of the week (1 - 7)                         |
+-------------------------------------------------------------------------+
| TIME ELEMENTS:                                                          |
| - HH / HH12   : Hour of day (01 - 12)                                   |
| - HH24        : Hour of day (00 - 23)                                   |
| - MI          : Minutes (00 - 59) [Do not confuse with MM!]             |
| - SS          : Seconds (00 - 59)                                       |
| - SSSSS       : Seconds elapsed since midnight (0 - 86399)              |
| - AM / PM     : Meridian indicator                                      |
+-------------------------------------------------------------------------+

Case Sensitivity in Date Formatting

Oracle dynamically mirrors the capitalization used in the format model:

• TO_CHAR(d, 'MONTH') $\rightarrow$ 'MAY '
• TO_CHAR(d, 'Month') $\rightarrow$ 'May '
• TO_CHAR(d, 'month') $\rightarrow$ 'may '

Format Modifiers: fm, sp, th

Format modifiers alter the default output behavior of format elements:

1. fm (Fill Mode): By default, MONTH and DAY return strings blank-padded to 9 characters, and numbers have leading zeros (e.g. 05). The fm prefix suppresses leading zeros and eliminates trailing blanks. It acts as a toggle:

   -- Default (padded):
   SELECT '>' || TO_CHAR(DATE '2026-05-04', 'Month DD, YYYY') || '<' FROM dual;
   --> '>May       04, 2026<'
   
   -- With fm modifier (compact):
   SELECT '>' || TO_CHAR(DATE '2026-05-04', 'fmMonth DD, YYYY') || '<' FROM dual;
   --> '>May 4, 2026<'
   

2. sp (Spell Out): Spells out a numeric component as English words:

   SELECT TO_CHAR(DATE '2026-05-15', 'DDsp') FROM dual; --> 'FIFTEEN'
   

3. th (Ordinal): Appends the English ordinal suffix (st, nd, rd, th):

   SELECT TO_CHAR(DATE '2026-05-15', 'DDth') FROM dual; --> '15TH'
   SELECT TO_CHAR(DATE '2026-05-15', 'Ddth') FROM dual; --> '15th'
   

4. Combined spth / thsp: Spells out an ordinal number:

   SELECT TO_CHAR(DATE '2026-05-15', 'fmDdspth "of" Month, Year') FROM dual;
   --> 'Fifteenth of May, Twenty Twenty-Six'
   

Embedding Literal Text in Format Models

To include arbitrary text strings within a format model without Oracle attempting to parse them as format elements, enclose the literal text in double quotation marks (""):

SELECT TO_CHAR(DATE '2026-05-15', 'fm"Today is "Day, "the "Ddspth" day of "Month')
FROM dual;
-- Result: Today is Friday, the Fifteenth day of May

---

Century Calculation: RR vs. YY Format Elements

When converting a 2-digit year string into an Oracle date, the choice between YY and RR determines which century is assigned.

• YY Format: The century is always the current century of the database system date.
• RR Format: Evaluates the 2-digit specified year against the current 2-digit system year using a 4-quadrant decision matrix to intelligently resolve 20th vs 21st century values.

+-------------------------------------------------------------------------+
|                    RR CENTURY CALCULATION DECISION MATRIX               |
+-----------------------+-------------------------------------------------+
|                       |              SPECIFIED 2-DIGIT YEAR             |
|   CURRENT YEAR        +-----------------------+-------------------------+
|  (Last 2 Digits)      |      00 - 49          |         50 - 99         |
+-----------------------+-----------------------+-------------------------+
|      00 - 49          |    CURRENT CENTURY    |    PREVIOUS CENTURY     |
| (e.g. Current = 2026) | '24' -> 2024          | '95' -> 1995            |
+-----------------------+-----------------------+-------------------------+
|      50 - 99          |     NEXT CENTURY      |     CURRENT CENTURY     |
| (e.g. Current = 1998) | '24' -> 2024          | '95' -> 1995            |
+-----------------------+-----------------------+-------------------------+

Comparison Table (Assuming Current Year is 2026):

Exam Rule for RRRR: If a 4-digit year is supplied to RRRR, it accepts all 4 digits as-is (e.g. '1984' $\rightarrow$ 1984). If only 2 digits are supplied to RRRR, it applies the exact same logic as RR.

---

The TO_DATE Function

The TO_DATE(char_string, [format_model], [nls_parameters]) function parses a formatted character string and converts it into an internal Oracle DATE binary structure.

Date Defaults:

If format elements are omitted in TO_DATE:

• Missing time elements default to midnight (00:00:00).
• Missing day defaults to the 1st day of the specified month.
• Missing month defaults to the current month of the system date.

The FX (Format Exact) Modifier

By default, TO_DATE is lenient: it ignores extra whitespace, leading zeros, and punctuation mismatches. When the FX modifier is specified at the beginning of the format model, Oracle enforces exact matching:

-- Lenient Default (Succeeds):
SELECT TO_DATE('May  5, 2026', 'Month DD, YYYY') FROM dual; --> 05-MAY-2026

-- FX Strict Matching (Fails with ORA-01861 due to double space and single digit):
SELECT TO_DATE('May  5, 2026', 'FXMonth DD, YYYY') FROM dual; 
-- Error: ORA-01861: literal does not match format string

-- FX Exact Match (Succeeds):
SELECT TO_DATE('May 05, 2026', 'FXMonth DD, YYYY') FROM dual; --> 05-MAY-2026

---

The TO_NUMBER Function

The TO_NUMBER(char_string, [format_model], [nls_parameters]) function converts a formatted character string containing currency symbols, group separators, or decimal markers into an internal Oracle NUMBER.

-- Converting a formatted currency string:
SELECT TO_NUMBER('$12,500.50', '$99,999.99') AS cleaned_num FROM dual;
-- Result: 12500.5

-- Converting with explicit NLS numeric characters (European format: '.' group, ',' decimal):
SELECT TO_NUMBER('12.500,50', '99G999D99', 'NLS_NUMERIC_CHARACTERS='',.''') 
FROM dual;
-- Result: 12500.5