SQL & PL/SQL

Hi There,

I've strange date data storage format, please could anyone help/comment on the following issue? I've date column in a table and trying to dump along with another user date. But the type and bytes are looking quite different? is that bug or any clarificaitons? As you can see it shows type 12 in one case and another 13 at the same time both are date data types and dump shows 7 and 8 bytes?

SQL> select * from v$version;

BANNER
------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi

SQL> DESC MY_TABLE;

NAME Null? Type
------------------------------- --------- -----
PK_ID NOT NULL NUMBER
EXPIRY_DATE DATE

SQL> select EXPIRY_DATE DB_CDT, dump(EXPIRY_DATE) DB_CDT_DUMP,
to_date('00010101','YYYYMMDD') MY_CDT, dump(to_date('00010101','YYYYMMDD')) MY_CDT_DUMP
from MY_TABLE
where TO_CHAR(EXPIR_DATE, 'DD.MM.YYYY') = '01.01.0001';

DB_CDT DB_CDT_DUMP MY_CDT MY_CDT_DUMP
---------- ------------------------------- ---------- -----------------------------
01.01.0001 Typ=12 Len=7: 100,101,1,1,1,1,1 01.01.0001 Typ=13 Len=8: 0,1,1,1,0,0,0,0
01.01.0001 Typ=12 Len=7: 100,101,1,1,1,1,1 01.01.0001 Typ=13 Len=8: 0,1,1,1,0,0,0,0
01.01.0001 Typ=12 Len=7: 100,101,1,1,1,1,1 01.01.0001 Typ=13 Len=8: 0,1,1,1,0,0,0,0
01.01.0001 Typ=12 Len=7: 100,101,1,1,1,1,1 01.01.0001 Typ=13 Len=8: 0,1,1,1,0,0,0,0
01.01.0001 Typ=12 Len=7: 100,101,1,1,1,1,1 01.01.0001 Typ=13 Len=8: 0,1,1,1,0,0,0,0
01.01.0001 Typ=12 Len=7: 100,101,1,1,1,1,1 01.01.0001 Typ=13 Len=8: 0,1,1,1,0,0,0,0

SQL>

Thanks,
-Chan.

Message was edited by:
chanukm