| 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: internal date value
Oops...
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
Peter Gram
Sent: Friday, January 23, 2004 2:54 PM
To: Multiple recipients of list ORACLE-L
Igor
Sorry but Oracle uses 7 bytes for a date
century (1 byte)
year (1 byte)
month (1 byte)
day (1 byte)
hour (1 byte)
minute (1 byte)
second (1 byte)
SQL> desc d
Name Null? Type ----------------------------------------- -------- ---------------------------- D DATE
SQL> col dump format a40
SQL> select to_char(d, 'dd mon rrrr hh24:mi:ss'), dump(d) dump from d;
TO_CHAR(D,'DDMONRRRR DUMP
-------------------- ---------------------------------------- 05 may 0001 15:30:45 Typ=12 Len=7: 100,101,5,5,16,31,46 05 may 0100 15:30:45 Typ=12 Len=7: 101,100,5,5,16,31,46 05 may 0500 15:30:45 Typ=12 Len=7: 105,100,5,5,16,31,46 05 may 1000 15:30:45 Typ=12 Len=7: 110,100,5,5,16,31,46 05 may 1500 15:30:45 Typ=12 Len=7: 115,100,5,5,16,31,46 05 may 1999 15:30:45 Typ=12 Len=7: 119,199,5,5,16,31,46 05 may 2000 15:30:45 Typ=12 Len=7: 120,100,5,5,16,31,46 05 may 2100 15:30:45 Typ=12 Len=7: 121,100,5,5,16,31,46 05 may 2500 15:30:45 Typ=12 Len=7: 125,100,5,5,16,31,46 05 may 2600 15:30:45 Typ=12 Len=7: 126,100,5,5,16,31,4605 may 2900 15:30:45 Typ=12 Len=7: 129,100,5,5,16,31,46 05 may 3000 15:30:45 Typ=12 Len=7: 130,100,5,5,16,31,46
12 rows selected.
/peter
Igor Neyman wrote:
>You gather it wrong :)
>Oracle stores date in 8 bytes, one for each: year, month, day, hour,
>min, ... etc.
>
>Igor Neyman, OCP DBA
>ineyman_at_perceptron.com
>
>
>
>-----Original Message-----
>Droogendyk, Harry
>Sent: Friday, January 23, 2004 12:40 PM
>To: Multiple recipients of list ORACLE-L
>
>Folks:
>
>>From what I gather, Oracle stores dates as the number of elapsed days
>since
>Jan 1, 1968. When I query a column of type 'DATE', it returns me the
>default format, dd-mon-yy. I know I can use
>to_char(date_col,'YYYYMMDD')
>etc... to define many output formats.
>
>What can I do to get the raw internal value of the date? i.e. today is
>13172.
>
>Thanks.
>
>
-- Best regards/Venlig hilsen /*Peter Gram*/ <mailto:peter.gram_at_miracleas.dk> Miracle A/S <http://www.miracleas.dk/> Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: peter.gram_at_miracleas.dk <mailto:peter.gram_at_miracleas.dk> SQL Server Master Class 8-10 Marts, Database Forum 28-30 October Master Class 17-19 Januar 2005. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram INET: peter.gram_at_miracleas.dk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: ineyman_at_perceptron.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Jan 23 2004 - 14:34:51 CST
 |
 |