| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> SV: Date Format: Mystery
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
=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
-----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
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
=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
--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">
<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></FONT></SPAN> </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> </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> </DIV>
<DIV><FONT face=3D"Courier New" color=3D#0000ff size=3D2><SPAN=20
class=3D989585006-30012004>I have made a little test. =
First I dump a=20
SYSDATE to see the internal representation. Then I create a table =
with a=20
SYSDATE and dump it.</SPAN></FONT></DIV>
<DIV><SPAN class=3D989585006-30012004><FONT face=3D"Courier New" =
color=3D#0000ff=20
size=3D2>SQL> select to_char(sysdate, 'YYYY.MM.DD =
HH24:MI:SS'),=20
dump(sysdate)<BR> 2 from dual;</FONT></SPAN></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </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 =20
as<BR> 2 select sysdate d<BR> 3 from=20 dual;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>Table created.</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>SQL> select to_char(d,=20'YYYY.MM.DD HH24:MI:SS'), dump(d)<BR> 2 from=20 testdate;</FONT></DIV>
size=3D2>TO_CHAR(D,'YYYY.MM.D<BR>--------------------<BR>DUMP(D)<BR>-----= -----------------------------------------------------------------<BR>2004=.01.30 =20
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> </DIV>
<DIV><FONT size=3D+0><SPAN class=3D989585006-30012004><FONT =
face=3D"Courier New"=20
color=3D#0000ff size=3D2>SQL> select to_char(systimestamp, =
'YYYY.MM.DD=20
HH24:MI:SS.SSSSSSSSS'), dump(systimestamp)<BR> 2 from=20
dual;</FONT></SPAN></FONT></DIV> <DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT> </DIV> <DIV><FONT size=3D+0><SPAN class=3D989585006-30012004><FONT =face=3D"Courier New"=20
size=3D2>TO_CHAR(SYSTIMESTAMP,'YYYY.MM<BR>-----------------------------<B= R>DUMP(SYSTIMESTAMP)<BR>-------------------------------------------------= ---------------------<BR>2004.01.30=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> 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> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D286535700-30012004> 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> </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
=
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> INET: Rajesh.Pillai_at_nordstrom.com<BR><BR>Fat =
City=20
Network Services -- 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 -- 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). 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 Waddinxveen<BR>The Netherlands<BR>tel.=20
<X-TAB> </X-TAB>+31 (0) 182 640 428<BR>fax=20
<X-TAB> </X-TAB>+31 (0) 182 640=20
429<BR>mobile<X-TAB> </X-TAB>+31 (0) 653 911 =
950<BR>e-mail=20
=
--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
 |
 |