SQL Snippets ™: Tutorials for Oracle Developers

The Format eXact modifier, FX, toggles exact format matching on or off. It is used only in input format models (e.g. with TO_DATE). It has no effect when used in output format models (e.g. with TO_CHAR). When toggled on, FX forces the function to perform an exact match between the format model and the character argument. For example, these values work without error when FX is not specified

column b format a20
column c format a30

select
  to_date( '123', 'yyyy' )                as a, -- three digit year, not four
  to_timestamp( '2007    12', 'yyyy mm' ) as b, -- extra spaces
  to_timestamp_tz( '2007/12', 'yyyy-mm' ) as c  -- "/" instead of "-"
from dual ;
 

A           B                    C
----------- -------------------- ------------------------------
 0123-06-01  2007-12-01 00:00:00  2007-12-01 00:00:00 -04:00
 

while the same values will generate errors when FX is specified.

select to_date( '123', 'FXyyyy' ) as a from dual ;
select to_date( '123', 'FXyyyy' ) as a from dual
                *
ERROR at line 1:
ORA-01862: the numeric value does not match the length of the format item


 

select to_timestamp( '2007    12', 'FXyyyy mm' ) as b from dual ;
select to_timestamp( '2007    12', 'FXyyyy mm' ) as b from dual
                     *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


 

select to_timestamp_tz( '2007/12', 'FXyyyy-mm' ) as c from dual ;
select to_timestamp_tz( '2007/12', 'FXyyyy-mm' ) as c from dual
                        *
ERROR at line 1:
ORA-01861: literal does not match format string