Close

Timestamps for the year 12800?!

Just found this data yesterday, not sure when or how it got into the real table.
Ironically the column that is corrupt is the mod_date which could have told me when.
Note I can only display 2 digits of the year. If I try using yyyy format I’ll get an ORA-01877.

I think it’s interesting that even though the data is invalid, Oracle is still able to evaluate it correctly.

SQL> select count(*) from save_bad_timestamp_example where mod_date > TIMESTAMP '9999-12-31 23:59:59.999999999';

  COUNT(*)
----------
        15

SQL> select mod_date,dump(mod_date) from save_bad_timestamp_example;

MOD_DATE                       DUMP(MOD_DATE)
----------------------------   -------------------------------------
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM   Typ=180 Len=7: 228,100,7,24,21,38,59

15 rows selected.

SQL>

Leave a Reply