sql - Unable to understand how value converted to date format implicitly

Question

As I checked my NLS_DATE_FORMAT is DD-MM-RR.

Considering mytable with a date column,when I execute the following statement, it throws error "ORA-01840: input value not long enough for date format"which is reasonably understandable.

insert into mytable values('10'); //Error: reason understandable

Then why is following statement valid where clearly the input size is greater than size of default DATE Format.

insert into mytable values('10AUGUST2016'); //reason not understandable

Also here I did not use any TO_DATE function, I guess implicit conversion is taking place?

Answer 1

The String-to-Date Conversion Rules allow additional formatting rules (without any other modifiers being applied). So:

• MM also matches MON and MONTH;
• MON matches MONTH (and vice versa);
• RR matches RRRR; and
• The punctuation is optional.

So:

SELECT TO_DATE( '10AUGUST2016', 'DD-MM-RR'    ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUGUST2016', 'DD-MON-RR'   ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUGUST2016', 'DD-MONTH-RR' ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MM-RR'    ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MON-RR'   ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MONTH-RR' ) FROM DUAL;

All generate the date 2016-08-10T00:00:00.

You can prevent this by using the FX format model

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 zeroes.

  When FX is enabled, you can disable this check for leading zeroes 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.

Then:

SELECT TO_DATE( '10-AUGUST-2016', 'FXDD-MM-RR'    ) FROM DUAL;

Gives: ORA-01858: a non-numeric character was found where a numeric was expected and would only match where an exact pattern match is found (although RR will still match RRRR).

I guess implicit conversion is taking place?

Yes, oracle is implicitly using TO_DATE( '10AUGUST2016', NLS_DATE_FORMAT ) to do the conversion.

If you use:

ALTER SESSION SET NLS_DATE_FORMAT = 'FXDD-MM-RR';

Then your insert will fail.