Oracle: Why dateStr and dateFmt using different date format can work well in Oracle TO_DATE( dateStr, dateFmt)?

Question

Oracle version:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Test:

select TO_DATE('2022.02.21','YYYY-MM-DD') from dual;         --- convert success  
select TO_DATE('20220221','YYYY-MM-DD') from dual;          --- convert success  
select TO_DATE('2022/02/21','YYYY-MM-DD') from dual;        --- convert success  

Why dateStr and dateFmt using different date format can work well in Oracle TO_DATE( dateStr, dateFmt)?

Answer 1

Oracle will ignore punctuation characters:

String-to-Date Conversion Rules

The following additional formatting rules apply when converting string values to date values (unless you have used the FX or FXFM modifiers in the format model to control exact format checking):

• You can omit punctuation included in the format string from the date string if all the digits of the numerical format elements, including leading zeros, are specified. For example, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY.
• You can omit time fields found at the end of a format string from the date string.
• You can use any non-alphanumeric character in the date string to match the punctuation symbol in the format string.
• If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-20.

Table 2-20 Oracle Format Matching

Original Format Element	Additional Format Elements to Try in Place of the Original
'MM'	'MON' and 'MONTH'
'MON'	'MONTH'
'MONTH'	'MON'
'YY'	'YYYY'
'RR'	'RRRR'

Unless you use the FX format modifier:

FX

Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function:

• Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.
• The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks.
• Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeros.
• When FX is enabled, you can disable this check for leading zeros by using the FM modifier as well.

If any portion of the character argument violates any of these conditions, then Oracle returns an error message.

For example, given the queries:

select TO_DATE('2022.02.21','fxYYYY-MM-DD') from dual;
select TO_DATE('20220221','fxYYYY-MM-DD') from dual;
select TO_DATE('2022/02/21','fxYYYY-MM-DD') from dual;
select TO_DATE('2022-02-21','fxYYYY-MM-DD') from dual; 

Then only the last one will work and the first 3 will raise the exception:

ORA-01861: literal does not match format string

db<>fiddle here