Re: Reading DUMP of DATE field
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 25 Feb 2004 14:31:35 -0700
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@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
