First let’s understand what DUMP function do in Oracle.

The DUMP function shows the datatype, length in bytes and the actual value of each byte for column.

Now let’s do some hands-on:

SQL> create table dummy ( col1 DATE );

Table created.

SQL> INSERT INTO dummy
2 VALUES ( TO_DATE( ‘25/12/1980 12:30:05′,’DD/MM/YYYY HH24:MI:SS’ ) );

1 row created.

SQL> INSERT INTO dummy
2 VALUES ( TO_DATE( ‘25/12/2006 20:25:10′,’DD/MM/YYYY HH24:MI:SS’ ) );

1 row created.

SQL> select TO_CHAR(col1,’DD/MM/YYYY HH24:MI:SS’) col1, DUMP(col1) dumpcol1 FROM dummy

COL1

——————-

DUMPCOL1

——————————————————————————–

25/12/1980 12:30:05

Typ=12 Len=7: 119,180,12,25,13,31,6

25/12/2006 20:25:10

Typ=12 Len=7: 120,106,12,25,21,26,11

Now lets try to understand how Oracle is storing the DATE with above results.

Oracle’s stores DATE in total of 7 bytes. Each byte in it stores values for an element of the DATE as follows:

1st Byte: Stores the century value but before storing it add 100 to it.

2nd Byte: Stores the year and 100 is added to it before storing.

3rd Byte: Stores the Month.

4rth Byte: Stores the Day of the month.

5th Byte: Stores the hours but add 1 before storing it.

6th Byte: Stores the minutes but add 1 before storing it.

7th Byte: Stores the seconds but add 1 before storing it.