Oracle Timestamp Issue with date
Is oracle doing a fair job is storing correct dates for timestamp ?
Even though NLS_TIME_FORMAT is DD-MON-RR HH.MI.SSXFF AM and timestamp column accepts an insert with the date in DD-MON-YYYY format but Stores the first 2 YY as the YY of the year and last 2YY as the hour! Example below illustrates it. This is repoducible in 10G as well. Ideally it should error out instead of storing the wrong date.
This specifically happens when the year is < 12 and above 12 it behaves correctly!
SQL>select version from v$instance;
VERSION
-----------------
11.2.0.3.0
SQL>
SQL>select * from nls_database_parameters
Even though NLS_TIME_FORMAT is DD-MON-RR HH.MI.SSXFF AM and timestamp column accepts an insert with the date in DD-MON-YYYY format but Stores the first 2 YY as the YY of the year and last 2YY as the hour! Example below illustrates it. This is repoducible in 10G as well. Ideally it should error out instead of storing the wrong date.
This specifically happens when the year is < 12 and above 12 it behaves correctly!
SQL>select version from v$instance;
VERSION
-----------------
11.2.0.3.0
SQL>
SQL>select * from nls_database_parameters
0