Did some investigation on internal date data format:
A date in binary format contains seven bytes, as shown in table below:
BYTE 1 2 3 4 5 6 7
Meaning Century Year Month Day Hour Minute Second
Example 120 110 11 30 16 18 1
The century and year bytes are in an excess–100 notation.
Dates Before Common Era (BCE) are less than 100.
The era begins on 01–JAN–4712 BCE, which is Julian day 1.
For this date, the century byte is 53, and the year byte is 88.
The hour, minute, and second bytes are in excess–1 notation.
The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60.
If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).
You can use DUMP function to investigate this:
SQL> create table t ( x date );
Table created.
SQL> insert into t values ( to_date( '03-nov-2010 3:17:01pm', 'dd-mon-yyyy hh:mi:sspm' ) );
1 row created.
SQL> commit;
SQL> select dump(x) from t;
DUMP(X)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,110,11,3,16,18,2
No comments:
Post a Comment