Re: Date datatype

A copy of this was sent to "fumi" <fumi_at_tpts5.seed.net.tw> (if that email address didn't require changing) On 14 Oct 1999 16:51:10 GMT, you wrote:

>Hi,
>
>I found something strange about the date datatype.
>See the following script.
>The internal representations of DATE in a table
>and in a in-line statement are different.
>
>Does anyone know why Oracle uses two different internal representations of
>DATE?
>
>
>---- begin script ----
>SQL> create table test (d date);
>
>Table created.
>
>SQL> insert into test values (to_date('2000/01/01', 'yyyy/mm/dd'));
>
>1 row created.
>
>SQL> select dump(d) from test;
>
>DUMP(D)
>-------------------------------------------------------------------
>Typ=12 Len=7: 120,100,1,1,1,1,1
>
>SQL> select dump(to_date('2000/01/01', 'yyyy/mm/dd')) from dual;
>
>DUMP(TO_DATE('2000/01/01','YYYY
>-------------------------------
>Typ=13 Len=8: 208,7,1,1,0,0,0,0
>
>

for a date that does not live in the database (and hence is only available on that platform and will never be exported, never needs to move to another system) we use a machine dependent format for that date (to make it marginally faster).

External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations. Note that the same result can be seen when DUMPing the value SYSDATE.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation