LLI Wiki — My Digital Knowledge Base

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:

Notes

Mastodon LLIMastodon SocialFosstodon