Study guide
5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions
Oracle Database SQL Certified Associate 1Z0-071
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Relational Database Concepts & Logical Modeling
1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals
2 Chapter 2: Retrieving Data with SQL SELECT
2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection
3 Chapter 3: Restricting and Sorting Query Data
3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives
4 Chapter 4: Customizing Output with Single-Row Functions
4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions
5 Chapter 5: Conversion Functions and Conditional Expressions
5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE
6 Chapter 6: Reporting Aggregated Data with Group Functions
6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause
7 Chapter 7: Displaying Data from Multiple Tables (Joins)
7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL
8 Chapter 8: Solving Queries with Subqueries
8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps
9 Chapter 9: Set Operators
9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules
10 Chapter 10: Managing Tables with DML & Transaction Control
10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT
11 Chapter 11: Managing Tables and Relationships with DDL
11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables
12 Chapter 12: Views
12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints
13 Chapter 13: Indexes, Synonyms, and Sequences
13.1 Sequences 13.2 Indexes 13.3 Synonyms
14 Chapter 14: Controlling User Access & Privileges
14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management
15 Chapter 15: Oracle Data Dictionary Views
15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments
16 Chapter 16: Managing Data in Different Time Zones
16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment
5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions
Key Takeaways Explain This Topic AI
The three primary explicit conversion functions in Oracle SQL are TO_CHAR (converts numbers/dates to formatted strings), TO_DATE (converts character strings to DATE), and TO_NUMBER (converts formatted character strings to NUMBER).
Number format models utilize elements such as 9 (digit/blank), 0 (leading/trailing zero), $ (dollar sign), L (local currency), D (decimal separator), and G (grouping separator); integer overflow returns a string of hash symbols (###).
Date format models provide extensive elements including YYYY, RRRR, YEAR, MM, MON, MONTH, DD, DAY, DY, HH24, MI, and SS; case sensitivity in output mirrors the capitalization of the format model (e.g., 'Month' vs 'MONTH').
The 'fm' (fill mode) modifier suppresses leading zeros and trailing blank padding for month and day names, while 'sp' spells out numbers and 'th' generates ordinal numbers.
The 'RR' format model dynamically resolves 2-digit years to either the 20th or 21st century based on a 4-quadrant decision matrix comparing the current year (00-49 vs 50-99) to the specified year.
Last updated: August 2026
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
Format Element Description Example Input Format Mask Result Output
9 Represents a numeric digit. Suppresses leading zeros (displays leading space for positive sign). 1234.5 '9999.99' ' 1234.50'
- 0 Forces leading or trailing zeros to be displayed. 45 '00999' '00045'
- $ Prefixes a leading dollar sign. 2500 '$99,999' '$2,500'
- L Uses the session's Local Currency Symbol (from NLS_CURRENCY ). 1200 'L99,999' '€1,200' (if EUR)
- C Uses the session's ISO Currency Code (from NLS_ISO_CURRENCY ). 1200 'C99,999' 'USD1,200'
- D Explicit Decimal separator character (from NLS_NUMERIC_CHARACTERS ). 123.45 '999D99' ' 123.45'
- G Explicit Grouping / thousands separator (from NLS_NUMERIC_CHARACTERS ). 1000000 '9G999G999' ' 1,000,000'
- MI Appends a trailing minus sign for negative numbers; trailing blank for positive. -500 '9999MI' '500-'
- PR Encloses negative numbers in angle brackets ( <500> ); spaces for positive. -500 '9999PR' '<500>'
- S Prefixes or suffixes an explicit plus ( + ) or minus ( - ) sign. 150 'S9999' '+150'
- EEEE Formats output in scientific exponential notation. 12345 '9.99EEEE' ' 1.23E+04'
- B Returns blanks for integer values that equal zero. 0 'B9999' ' '
V Multiplies the input value by 10 n 10^n 1 0 n (where n n n is the number of 9s following V ). 125 '999V99' '12500'
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:
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<'
sp (Spell Out): Spells out a numeric component as English words: SELECT TO_CHAR(DATE '2026-05-15', 'DDsp') FROM dual; --> 'FIFTEEN'
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'
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):
String Literal Format Mask Interpreted Year Rule Applied
'15-MAY-26' 'DD-MON-YY' 2026 YY always uses current century ( 20xx ).
'15-MAY-95' 'DD-MON-YY' 2095 YY always uses current century ( 20xx ), yielding an unintended future date!
'15-MAY-26' 'DD-MON-RR' 2026 Current year (26) is 00-49; Specified (26) is 00-49 → \rightarrow → Current century ( 2026 ).
'15-MAY-95' 'DD-MON-RR' 1995 Current year (26) is 00-49; Specified (95) is 50-99 → \rightarrow → Previous century ( 1995 ).
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