Oracle DATE internal format and out-of-range dates
Decode DATE bytes with DUMP (types 12 and 13) and insert a date from its raw value with DBMS_STATS.CONVERT_RAW_VALUE.
An Oracle DATE column is stored in 7 bytes. The DUMP function shows these raw bytes, which helps to understand an unexpected value or to check a date received from another source.
Showing the internal representation
SELECT dt_col,
DUMP(dt_col),
TO_CHAR(dt_col, 'SYYYY/MM/DD HH24:MI:SS')
FROM t;
Result line for the date 9999-01-01:
01-JAN-99 Typ=12 Len=7: 199,199,1,1,1,1,1 9999/01/01 00:00:00
Decoding the 7 bytes (type 12)
| Byte | Content | Value calculation |
|---|---|---|
| 1 | Century | value - 100 |
| 2 | Year | value - 100 |
| 3 | Month | value |
| 4 | Day | value |
| 5 | Hour | value - 1 |
| 6 | Minute | value - 1 |
| 7 | Second | value - 1 |
Examples:
100,112,2,7,1,1,1is0012-02-07 00:00:00(century 0, year 12, month 2, day 7, hour, minute and second at 0).199,199,1,1,1,1,1is9999-01-01 00:00:00.
Notes
- Hours, minutes and seconds are stored with an offset of 1: the value 1 means 0.
- The
DBMS_STATS.CONVERT_RAW_VALUEexample from the source note is not included, because its displayed result was not consistent with the described format. - The section on type 13 (
SYSDATE) was removed because this representation could not be verified.