| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Reading DUMP of DATE field
The format of the date datatype is
byte 1 - century (excess 100) 120 - 100 = 20 byte 2 - year (excess 100) 104 - 100 = 04 byte 3 - month = 2 byte 4 - day = 20 byte 5 - hour (excess 1) 1 - 1 = 0 byte 6 - minute (excess 1) 1 - 1 = 0 byte 7 - seconds (excess 1) 1 - 1 = 0
So the YYYY/MM/DD:hh24:mi:ss format would be 2004/02/20:00:00:00
The reason for the excess is that the value cannot be stored as a 0, even though 0 is allowable in some fields. Obviously we don't have a month 0 nor day 0.
Daniel
"Jesse, Rich" wrote:
> Hey all,
>
> I'm trying to see if I can determine the actual date of a DATE column in
> 8.1.7.4.0 on HP/UX given a DUMP of the column. When I:
>
> SELECT DUMP(mydatecol)
> FROM mytab;
>
> I get this from the single row in the table:
>
> Typ=12 Len=7: 120,104,2,20,1,1,1
>
> From a translated value of "02/20/2004" in that column, I can make some
> assumptions as to the 2nd, 3rd, and 4th fields, but the rest baffles me. I
> checked the App Dev manuals on tahiti.oracle.com, but nothing popped out at
> me.
>
> The reason I'm checking on this is because of a known bug in OCI that allows
> putting values of "0" for every byte in the date field. This is invalid, of
> course, but OCI doesn't check that validity before setting the value of a
> date field. It really causes havoc in other tools, however. Some display a
> date in the 1800s, others blow up.
>
> TIA,
> Rich
>
> Rich Jesse System/Database Administrator
> rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Feb 25 2004 - 15:29:06 CST
 |
 |