Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> SV: Date Format: Mystery

SV: Date Format: Mystery

From: Jesper Haure Norrevang <jhn.aida_at_cbs.dk>
Date: Fri, 30 Jan 2004 02:13:43 -0800
Message-ID: <F001.005DE749.20040130021343@fatcity.com>


Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: quoted-printable

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

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

/Jesper

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

Rajesh,
=20

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).
=20

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;
=20

TO_CHAR(SYSDATE,'YYY



DUMP(SYSDATE)

2004.01.30 08:13:16
Typ=3D13 Len=3D8: 7,212,1,30,8,13,16,0
=20

=20

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

Table created.
=20

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

TO_CHAR(D,'YYYY.MM.D



DUMP(D)

2004.01.30 08:13:16
Typ=3D12 Len=3D7: 120,104,1,30,9,14,17
=20

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.
=20

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.
=20

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

TO_CHAR(SYSTIMESTAMP,'YYYY.MM

DUMP(SYSTIMESTAMP)

2004.01.30 08:13:16.295961616
Typ=3D188 Len=3D20: 7,212,1,30,7,13,16,0,6,14,35,240,1,0,5,46,0,0,32,32
=20

Regards
Jesper Haure N=F8rrevang

-----Oprindelig meddelelse-----
Fra: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] P=E5 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.=20
=20

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

Thanks,
Rajesh

-----Original Message-----
Sent: Thursday, January 29, 2004 4:29 PM To: Multiple recipients of list ORACLE-L

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 -=20

  1. select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS AM') from dual; result =3D 2004-01-29 12:52:06 PM
  2. select to_char(sysdate,'YYYY-MM-DD HH24:MI:SSSS AM') from dual; result =3D 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 =3D 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

--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net/>=20
--=20
Author: Pillai, Rajesh
  INET: Rajesh.Pillai_at_nordstrom.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com <http://www.fatcity.com/>=20
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).

=3D=3D=3D

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

DBA!ert, Independent Oracle Consultancy=20 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 info.dbalert_at_xs4all.nl

--Boundary_(ID_Eb+/TCvNW2MJ+iaxmgJhWw)
Content-type: text/html; charset=iso-8859-1 Content-transfer-encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>Meddelelse</TITLE>
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D645570810-30012004><FONT face=3D"Courier New" =
color=3D#0000ff=20
size=3D2>Sorry! I was mixing&nbsp;format-masks. The last SQL should read = (i.e.=20
FFFF instead of SSSS):</FONT></SPAN></DIV> <DIV><SPAN class=3D645570810-30012004><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D645570810-30012004></SPAN><SPAN = class=3D645570810-30012004><FONT=20
face=3D"Courier New" color=3D#0000ff size=3D2>select = to_char(systimestamp, 'YYYY.MM.DD=20
HH24:MI:SS.FFFFFFFF'), dump(systimestamp)</FONT></DIV> <DIV><FONT face=3D"Courier New" color=3D#0000ff size=3D2>from = dual;</FONT></DIV>
<DIV>
<P><SPAN class=3D645570810-30012004><FONT face=3D"Courier New" = color=3D#0000ff=20
size=3D2>/Jesper</FONT></SPAN></P></SPAN></DIV> <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">   <DIV><FONT face=3DTahoma size=3D2>-----Oprindelig = meddelelse-----<BR><B>Fra:</B>=20
  ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] <B>P=E5 vegne af = </B>Jesper=20
  Haure Norrevang<BR><B>Sendt:</B> 30. januar 2004 08:24<BR><B>Til:</B> = Multiple=20
  recipients of list ORACLE-L<BR><B>Emne:</B> SV: Date Format:=20   Mystery<BR><BR></FONT></DIV>
  <DIV><SPAN class=3D989585006-30012004><FONT face=3D"Courier New" = color=3D#0000ff=20
  size=3D2>Rajesh,</FONT></SPAN></DIV>
  <DIV><FONT color=3D#0000ff><SPAN class=3D989585006-30012004><FONT=20   face=3D"Courier New" color=3D#0000ff = size=3D2></FONT></SPAN></FONT>&nbsp;</DIV>   <DIV><SPAN class=3D989585006-30012004><FONT face=3D"Courier New" = color=3D#0000ff=20
  size=3D2>SYSDATE is of datatype DATE (that's what the documentation = says), i.e.=20
  it contains century, year, month, day, hour, minute and second = (without=20
  decimals).</FONT></SPAN></DIV>
  <DIV><FONT color=3D#0000ff><SPAN class=3D989585006-30012004><FONT=20   face=3D"Courier New" color=3D#0000ff = size=3D2></FONT></SPAN></FONT>&nbsp;</DIV>   <DIV><FONT face=3D"Courier New" color=3D#0000ff size=3D2><SPAN=20   class=3D989585006-30012004>I have made a little test. = First&nbsp;I&nbsp;dump a=20
  SYSDATE to see the internal representation. Then I&nbsp;create a table = with a=20
  SYSDATE and&nbsp;dump it.</SPAN></FONT></DIV>   <DIV><SPAN class=3D989585006-30012004><FONT face=3D"Courier New" = color=3D#0000ff=20
  size=3D2>SQL&gt; select to_char(sysdate, 'YYYY.MM.DD&nbsp; = HH24:MI:SS'),=20
  dump(sysdate)<BR>&nbsp; 2&nbsp; from dual;</FONT></SPAN></DIV>   <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>   <DIV><SPAN class=3D989585006-30012004><FONT face=3D"Courier New" = color=3D#0000ff=20
  =

size=3D2>TO_CHAR(SYSDATE,'YYY<BR>--------------------<BR>DUMP(SYSDATE)<BR=
>----------------------------------------------------------------------<B=
R>2004.01.30&nbsp;=20
  08:13:16<BR>Typ=3D13 Len=3D8: 7,212,1,30,8,13,16,0</FONT></SPAN></DIV>   <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV><SPAN=20   class=3D989585006-30012004><FONT color=3D#0000ff>   <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>   <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; create table testdate =

  as<BR>&nbsp; 2&nbsp; select sysdate d<BR>&nbsp; 3&nbsp; from=20 dual;</FONT></DIV>

  <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3D"Courier New" size=3D2>Table created.</FONT></DIV>
  <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; select to_char(d,=20
  'YYYY.MM.DD&nbsp; HH24:MI:SS'), dump(d)<BR>&nbsp; 2&nbsp; from=20   testdate;</FONT></DIV>
  <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>   <DIV><FONT face=3D"Courier New"=20
  =
size=3D2>TO_CHAR(D,'YYYY.MM.D<BR>--------------------<BR>DUMP(D)<BR>-----=
-----------------------------------------------------------------<BR>2004=
.01.30&nbsp;=20
  08:13:16<BR>Typ=3D12 Len=3D7: 120,104,1,30,9,14,17</FONT></FONT></DIV>   <DIV><FONT color=3D#0000ff><FONT face=3D"Courier New"=20   size=3D2></FONT></FONT>&nbsp;</DIV>
  <DIV><FONT size=3D+0><SPAN class=3D989585006-30012004><FONT = face=3D"Courier New"=20
  color=3D#0000ff size=3D2>Certainly som conversion is going on here. = This might be=20
  the reason why there has been confusion about 7 or 8 bytes in a DATE=20   datatype.</FONT></SPAN></FONT></DIV>
  <DIV><FONT face=3D"Courier New" color=3D#0000ff size=3D2><SPAN=20   class=3D989585006-30012004></SPAN></FONT>&nbsp;</DIV>   <DIV><FONT face=3D"Courier New" color=3D#0000ff size=3D2><SPAN=20   class=3D989585006-30012004>To answer your question, it is possible to = deal with=20
  fractions of seconds, byt you need to use the&nbsp;TIMESTAMP datatype. = The=20
  function SYSTIMESTAMP could be useful. Be aware that Oracle supports 9 =

  decimals, but not all hardware platforms do. Therefore the results = might be=20
  inaccurate.</SPAN></FONT></DIV>
  <DIV><FONT size=3D+0><SPAN class=3D989585006-30012004><FONT = face=3D"Courier New"=20
  color=3D#0000ff size=3D2></FONT></SPAN></FONT>&nbsp;</DIV>   <DIV><FONT size=3D+0><SPAN class=3D989585006-30012004><FONT = face=3D"Courier New"=20
  color=3D#0000ff size=3D2>SQL&gt; select to_char(systimestamp, = 'YYYY.MM.DD=20
  HH24:MI:SS.SSSSSSSSS'), dump(systimestamp)<BR>&nbsp; 2&nbsp; from=20

  dual;</FONT></SPAN></FONT></DIV>
  <DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT size=3D+0><SPAN class=3D989585006-30012004><FONT =
face=3D"Courier New"=20
  color=3D#0000ff=20
  =
size=3D2>TO_CHAR(SYSTIMESTAMP,'YYYY.MM<BR>-----------------------------<B=
R>DUMP(SYSTIMESTAMP)<BR>-------------------------------------------------=
---------------------<BR>2004.01.30=20

  08:13:16.295961616<BR>Typ=3D188 Len=3D20:=20   =
7,212,1,30,7,13,16,0,6,14,35,240,1,0,5,46,0,0,32,32</FONT></SPAN></SPAN><= /FONT></DIV>
  <DIV><FONT size=3D2><FONT color=3D#0000ff><SPAN = class=3D989585006-30012004><FONT=20
  face=3D"Courier New" color=3D#0000ff=20   size=3D2></FONT></SPAN></FONT></FONT>&nbsp;</DIV>   <DIV><FONT size=3D2><FONT face=3D"Courier New" color=3D#0000ff><SPAN=20   class=3D989585006-30012004>Regards</SPAN></FONT></FONT></DIV>   <DIV><FONT size=3D2><FONT face=3D"Courier New" color=3D#0000ff><SPAN=20   class=3D989585006-30012004>Jesper Haure = N=F8rrevang</SPAN></FONT></FONT></DIV>
  <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">     <DIV></DIV>
    <DIV class=3DOutlookMessageHeader lang=3Dda dir=3Dltr = align=3Dleft><FONT face=3DTahoma=20

    size=3D2>-----Oprindelig meddelelse-----<BR><B>Fra:</B> = ml-errors_at_fatcity.com=20

    [mailto:ml-errors_at_fatcity.com] <B>P=E5 vegne af </B>Pillai,=20     Rajesh<BR><B>Sendt:</B> 30. januar 2004 01:59<BR><B>Til:</B> = Multiple=20

    recipients of list ORACLE-L<BR><B>Emne:</B> RE: Date Format:=20     Mystery<BR><BR></FONT></DIV>
    <DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20     class=3D286535700-30012004>Carel-Jan,</SPAN></FONT></DIV>     <DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20     class=3D286535700-30012004>&nbsp;&nbsp;&nbsp; This explanation = helps. Thanks a=20

    lot. </SPAN></FONT></DIV>
    <DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20     class=3D286535700-30012004></SPAN></FONT>&nbsp;</DIV>     <DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20     class=3D286535700-30012004>&nbsp;&nbsp;&nbsp; Could you also answer = if=20

    displaying centiseconds or milliseconds or 1/10th of a second in = oracle is=20

    possible or not</SPAN></FONT></DIV>
    <DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20     class=3D286535700-30012004></SPAN></FONT>&nbsp;</DIV>     <DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20     class=3D286535700-30012004>Thanks,</SPAN></FONT></DIV>     <DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20     class=3D286535700-30012004>Rajesh</SPAN></FONT></DIV>     <BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">       <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT = face=3DTahoma=20

      size=3D2>-----Original Message-----<BR><B>From:</B> Carel-Jan = Engel=20

      [mailto:cjpengel.dbalert_at_xs4all.nl]<BR><B>Sent:</B> Thursday, = January 29,=20

      2004 4:29 PM<BR><B>To:</B> Multiple recipients of list=20
      ORACLE-L<BR><B>Subject:</B> Re: Date Format:=20
      Mystery<BR><BR></DIV></FONT><FONT size=3D3>Rajesh,<BR><BR>A look =
into the=20
      docs might help you:<BR><BR>In date-format SS means seconds as of =
the=20
      seconds 00-59 in a minute.<BR>SSSSS means seconds since midnight, =
thus 0 -=20
      86399<BR><BR>Compiling the statement the longest part is =
recocnized=20
      first.<BR><BR>So:<BR><BR>SS give 06 in your first =
example.<BR><BR>SSSS=20
      gives 20, but twice: 2020<BR><BR>SSSSSS consists of the SSSSS =
part,=20
      followed by an unrecocnized single S: error<BR><BR>SSSSSSSS =
consists of=20
      SSSSS, followed by SS, followed by an unrecognized S:=20
      error<BR><BR>SSSSSSSSSS is SSSSS SSSSS, so the result is 46439=20
      46439.<BR><BR>Regards, Carel-Jan<BR><BR><BR><BR>At 10:29 PM =
1/29/2004, you=20
      wrote:<BR>
      <BLOCKQUOTE class=3Dcite cite=3D"" type=3D"cite">Hi=20
        =

All,<BR><X-TAB>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</X-TAB>Se= e=20

        the following - <BR><BR>1) select to_char(sysdate,'YYYY-MM-DD = HH24:MI:SS=20

        AM') from dual;<BR>result =3D 2004-01-29 12:52:06 = PM<BR><BR><BR>2) select=20

        to_char(sysdate,'YYYY-MM-DD HH24:MI:SSSS AM') from = dual;<BR>result =3D=20

        2004-01-29 12:52:2020 PM<BR><BR>3) select = to_char(sysdate,'YYYY-MM-DD=20

        HH24:MI:SSSSSS AM') from dual;<BR>ORA-01821: date format not=20
        recognized<BR><BR>4) select to_char(sysdate,'YYYY-MM-DD =
HH24:MI:SSSSSSSS=20
        AM') from dual;<BR>ORA-01821: date format not =
recognized<BR><BR>5)=20
        select to_char(sysdate,'YYYY-MM-DD HH24:MI:SSSSSSSSSS AM') from=20
        dual;<BR>result =3D 2004-01-29 12:53:4643946439 PM<BR><BR>What =
does it=20
        mean? If I want to see the 10th part of the second or 100th part =
of the=20
        second, Is it poosible?<BR><BR>I would appreciate all your=20
        hints.<BR><BR>Thanks,<BR>Rajesh<BR><BR><BR><BR>-- <BR>Please see =
the=20
        official ORACLE-L FAQ: <A href=3D"http://www.orafaq.net/"=20
        eudora=3D"autourl">http://www.orafaq.net</A><BR>-- <BR>Author: =
Pillai,=20
        Rajesh<BR>&nbsp; INET: Rajesh.Pillai_at_nordstrom.com<BR><BR>Fat =
City=20
        Network Services&nbsp;&nbsp;&nbsp; -- 858-538-5051 <A=20
        href=3D"http://www.fatcity.com/"=20
        eudora=3D"autourl">http://www.fatcity.com</A><BR>San Diego,=20
        California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Mailing =
list and=20
        web hosting=20
        =
services<BR>-------------------------------------------------------------=
--------<BR>To=20
        REMOVE yourself from this mailing list, send an E-Mail =
message<BR>to:=20
        ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and =
in<BR>the=20
        message BODY, include a line containing: UNSUB ORACLE-L<BR>(or =
the name=20
        of mailing list you want to be removed from).&nbsp; You =
may<BR>also send=20
        the HELP command for other information (like=20
      subscribing).</FONT></BLOCKQUOTE><X-SIGSEP>
      <P></X-SIGSEP><FONT size=3D2>=3D=3D=3D<BR>If you think education =
is expensive, try=20
      ignorance. (Derek Bok)<BR>=3D=3D=3D<BR><BR></FONT><FONT =
color=3D#ff0000=20
      size=3D2>DBA</FONT><FONT color=3D#0000ff size=3D2>!ert, =
</FONT><FONT=20
      size=3D2>Independent Oracle Consultancy <BR>Kastanjelaan =
61C<BR>2743=20
      BX&nbsp; Waddinxveen<BR>The Netherlands<BR>tel.=20
      <X-TAB>&nbsp;&nbsp;&nbsp;</X-TAB>+31 (0) 182 640 428<BR>fax=20
      <X-TAB>&nbsp;&nbsp;&nbsp;&nbsp;</X-TAB>+31 (0) 182 640=20
      429<BR>mobile<X-TAB>&nbsp;&nbsp;</X-TAB>+31 (0) 653 911 =
950<BR>e-mail=20
      =

info.dbalert_at_xs4all.nl<BR><BR><BR><BR></FONT></P></BLOCKQUOTE></BLOCKQUOT= E></BLOCKQUOTE></BODY></HTML>

--Boundary_(ID_Eb+/TCvNW2MJ+iaxmgJhWw)--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesper Haure Norrevang
  INET: jhn.aida_at_cbs.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).
Received on Fri Jan 30 2004 - 04:13:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US