SV: Date Format: Mystery
Title: Meddelelse 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. FirstIdump a SYSDATE to see the internal representation. Then Icreate a table with a SYSDATE anddump it. SQL select to_char(sysdate, '.MM.DD HH24:MI:SS'), dump(sysdate) 2 from dual; TO_CHAR(SYSDATE,'YYYDUMP(SYSDATE)--2004.01.30 08:13:16Typ=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, '.MM.DD HH24:MI:SS'), dump(d) 2 from testdate; TO_CHAR(D,'.MM.DDUMP(D)--2004.01.30 08:13:16Typ=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 theTIMESTAMP 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, '.MM.DD HH24:MI:SS.S'), dump(systimestamp) 2 from dual; TO_CHAR(SYSTIMESTAMP,'.MM-DUMP(SYSTIMESTAMP)--2004.01.30 08:13:16.295961616Typ=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, RajeshSendt: 30. januar 2004 01:59Til: Multiple recipients of list ORACLE-LEmne: 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 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Date Format: MysteryRajesh,A look into the docs might help you:In date-format SS means seconds as of the seconds 00-59 in a minute.S means seconds since midnight, thus 0 - 86399Compiling the statement the longest part is recocnized first.So:SS give 06 in your first example. gives 20, but twice: 2020SS consists of the S part, followed by an unrecocnized single S: error consists of S, followed by SS, followed by an unrecognized S: errorSS is S S, so the result is 46439 46439.Regards, Carel-JanAt 10:29 PM 1/29/2004, you wrote: Hi All,See the following - 1) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;result = 2004-01-29 12:52:06 PM2) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual;result = 2004-01-29 12:52:2020 PM3) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;ORA-01821: date format not recognized4) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual;ORA-01821: date format not recognized5) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;result = 2004-01-29 12:53:4643946439 PMWhat 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.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso 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 61C2743 BX WaddinxveenThe Netherlandstel. +31 (0) 182 640 428fax +31 (0) 182 640 429mobile+31 (0) 653 911 950e-mail [EMAIL PROTECTED]
SV: Date Format: Mystery
Title: Meddelelse Sorry! I was mixingformat-masks. The last SQL should read (i.e. instead of ): select to_char(systimestamp, '.MM.DD HH24:MI:SS.'), dump(systimestamp) from dual; /Jesper -Oprindelig meddelelse-Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af Jesper Haure NorrevangSendt: 30. januar 2004 08:24Til: Multiple recipients of list ORACLE-LEmne: 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. FirstIdump a SYSDATE to see the internal representation. Then Icreate a table with a SYSDATE anddump it. SQL select to_char(sysdate, '.MM.DD HH24:MI:SS'), dump(sysdate) 2 from dual; TO_CHAR(SYSDATE,'YYYDUMP(SYSDATE)--2004.01.30 08:13:16Typ=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, '.MM.DD HH24:MI:SS'), dump(d) 2 from testdate; TO_CHAR(D,'.MM.DDUMP(D)--2004.01.30 08:13:16Typ=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 theTIMESTAMP 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, '.MM.DD HH24:MI:SS.S'), dump(systimestamp) 2 from dual; TO_CHAR(SYSTIMESTAMP,'.MM-DUMP(SYSTIMESTAMP)--2004.01.30 08:13:16.295961616Typ=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, RajeshSendt: 30. januar 2004 01:59Til: Multiple recipients of list ORACLE-LEmne: 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 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Date Format: MysteryRajesh,A look into the docs might help you:In date-format SS means seconds as of the seconds 00-59 in a minute.S means seconds since midnight, thus 0 - 86399Compiling the statement the longest part is recocnized first.So:SS give 06 in your first example. gives 20, but twice: 2020SS consists of the S part, followed by an unrecocnized single S: error consists of S, followed by SS, followed by an unrecognized S: errorSS is S S, so the result is 46439 46439.Regards, Carel-JanAt 10:29 PM 1/29/2004, you wrote: Hi All,See the following - 1) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;result = 2004-01-29 12:52:06 PM2) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual;result = 2004-01-29 12:52:2020 PM3) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;ORA-01821: date format not recognized4) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual;ORA-01821: date format not recognized5) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;result = 2004-01-29 12:53:4643946439 PMWhat 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.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want
Re: SV: Date Format: Mystery
Friday, January 30, 2004, 2:24:25 AM, Jesper Haure Norrevang ([EMAIL PROTECTED]) wrote: JHN Certainly som conversion is going on here. This might be the reason why JHN there has been confusion about 7 or 8 bytes in a DATE datatype. That's really interesting, that switch between 7 and 8 bytes. Oracle's docs, I believe in the OCI manual, do show a seven-byte format. JHN To answer your question, it is possible to deal with fractions of seconds, JHN byt you need to use the TIMESTAMP datatype. The function SYSTIMESTAMP could JHN be useful. Be aware that Oracle supports 9 decimals, but not all hardware JHN platforms do. Related to this, just because a platform returns, say, six digits, does not mean it increments on that last digit. Instead of: 21.01 seconds 21.02 seconds ... The best your platform does might look like: 21.01 seconds 21.000801 seconds 21.001601 seconds ... I just made these numbers up, but hopefully they give the idea. I'm still curious about that seven versus eight byte thing with SYSDATE. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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).
Re: SV: Date Format: Mystery
Hi, From Note: 69028.1 on Metalink The datatype returned is 13 and not 12, the external DATE datatype. This occurs because we rely on the TO_DATE function! External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the Len= value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations. Note that the same result can be seen when DUMPing the value SYSDATE. Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL PROTECTED] Jonathan Gennick [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L .com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: SV: Date Format: Mystery .com 30/01/04 13:44 Please respond to ORACLE-L Friday, January 30, 2004, 2:24:25 AM, Jesper Haure Norrevang ([EMAIL PROTECTED]) wrote: JHN Certainly som conversion is going on here. This might be the reason why JHN there has been confusion about 7 or 8 bytes in a DATE datatype. That's really interesting, that switch between 7 and 8 bytes. Oracle's docs, I believe in the OCI manual, do show a seven-byte format. JHN To answer your question, it is possible to deal with fractions of seconds, JHN byt you need to use the TIMESTAMP datatype. The function SYSTIMESTAMP could JHN be useful. Be aware that Oracle supports 9 decimals, but not all hardware JHN platforms do. Related to this, just because a platform returns, say, six digits, does not mean it increments on that last digit. Instead of: 21.01 seconds 21.02 seconds ... The best your platform does might look like: 21.01 seconds 21.000801 seconds 21.001601 seconds ... I just made these numbers up, but hopefully they give the idea. I'm still curious about that seven versus eight byte thing with SYSDATE. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). CONFIDENTIAL: The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage
