SV: Date Format: Mystery

[Jesper Haure Norrevang]

Title: Meddelelse

Sorry! I was mixing format-masks. The last SQL should read (i.e. FFFF instead of SSSS):

 

select to_char(systimestamp, 'YYYY.MM.DD HH24:MI:SS.FFFFFFFF'), dump(systimestamp)

from dual;

/Jesper

-----Oprindelig meddelelse-----
Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af Jesper Haure Norrevang
Sendt: 30. januar 2004 08:24
Til: Multiple recipients of list ORACLE-L
Emne: SV: Date Format: Mystery

Rajesh,

 

SYSDATE is of datatype DATE (that's what the documentation says), i.e. it contains century, year, month, day, hour, minute and second (without decimals).

 

I have made a little test. First I dump a SYSDATE to see the internal representation. Then I create a table with a SYSDATE and dump it.

SQL> select to_char(sysdate, 'YYYY.MM.DD  HH24:MI:SS'), dump(sysdate)
  2  from dual;

 

TO_CHAR(SYSDATE,'YYY
--------------------
DUMP(SYSDATE)
----------------------------------------------------------------------
2004.01.30  08:13:16
Typ=13 Len=8: 7,212,1,30,8,13,16,0

 

 

SQL> create table testdate as
  2  select sysdate d
  3  from dual;

 

Table created.

 

SQL> select to_char(d, 'YYYY.MM.DD  HH24:MI:SS'), dump(d)
  2  from testdate;

 

TO_CHAR(D,'YYYY.MM.D
--------------------
DUMP(D)
----------------------------------------------------------------------
2004.01.30  08:13:16
Typ=12 Len=7: 120,104,1,30,9,14,17

 

Certainly som conversion is going on here. This might be the reason why there has been confusion about 7 or 8 bytes in a DATE datatype.

 

To answer your question, it is possible to deal with fractions of seconds, byt you need to use the TIMESTAMP datatype. The function SYSTIMESTAMP could be useful. Be aware that Oracle supports 9 decimals, but not all hardware platforms do. Therefore the results might be inaccurate.

 

SQL> select to_char(systimestamp, 'YYYY.MM.DD HH24:MI:SS.SSSSSSSSS'), dump(systimestamp)
  2  from dual;

 

TO_CHAR(SYSTIMESTAMP,'YYYY.MM
-----------------------------
DUMP(SYSTIMESTAMP)
----------------------------------------------------------------------
2004.01.30 08:13:16.295961616
Typ=188 Len=20: 7,212,1,30,7,13,16,0,6,14,35,240,1,0,5,46,0,0,32,32

 

Regards

Jesper Haure Nørrevang

-----Oprindelig meddelelse-----
Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af Pillai, Rajesh
Sendt: 30. januar 2004 01:59
Til: Multiple recipients of list ORACLE-L
Emne: RE: Date Format: Mystery

Carel-Jan,

    This explanation helps. Thanks a lot.

 

    Could you also answer if displaying centiseconds or milliseconds or 1/10th of a second in oracle is possible or not

 

Thanks,

Rajesh

-----Original Message-----
From: Carel-Jan Engel [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 29, 2004 4:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Date Format: Mystery

Rajesh,

A look into the docs might help you:

In date-format SS means seconds as of the seconds 00-59 in a minute.
SSSSS means seconds since midnight, thus 0 - 86399

Compiling the statement the longest part is recocnized first.

So:

SS give 06 in your first example.

SSSS gives 20, but twice: 2020

SSSSSS consists of the SSSSS part, followed by an unrecocnized single S: error

SSSSSSSS consists of SSSSS, followed by SS, followed by an unrecognized S: error

SSSSSSSSSS is SSSSS SSSSS, so the result is 46439 46439.

Regards, Carel-Jan



At 10:29 PM 1/29/2004, you wrote:

Hi All,
        See the following -

1) select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS AM') from dual;
result = 2004-01-29 12:52:06 PM


2) select to_char(sysdate,'YYYY-MM-DD HH24:MI:SSSS AM') from dual;
result = 2004-01-29 12:52:2020 PM

3) select to_char(sysdate,'YYYY-MM-DD HH24:MI:SSSSSS AM') from dual;
ORA-01821: date format not recognized

4) select to_char(sysdate,'YYYY-MM-DD HH24:MI:SSSSSSSS AM') from dual;
ORA-01821: date format not recognized

5) select to_char(sysdate,'YYYY-MM-DD HH24:MI:SSSSSSSSSS AM') from dual;
result = 2004-01-29 12:53:4643946439 PM

What does it mean? If I want to see the 10th part of the second or 100th part of the second, Is it poosible?

I would appreciate all your hints.

Thanks,
Rajesh



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Pillai, Rajesh
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

===
If you think education is expensive, try ignorance. (Derek Bok)
===

DBA!ert, Independent Oracle Consultancy
Kastanjelaan 61C
2743 BX  Waddinxveen
The Netherlands
tel.    +31 (0) 182 640 428
fax     +31 (0) 182 640 429
mobile  +31 (0) 653 911 950
e-mail [EMAIL PROTECTED]