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>

Advertisements

Questions and Comments always welcome

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: