That's because the DATE data type in Oracle only stores year, month,
day, hours, minutes and seconds. It does not store milliseconds.
In Oracle 9i they introduced a new data type, TIMESTAMP (in several
configurations), which holds all the data a DATE variable holds
including fractional part of a second.
Sysdate returns a DATE data type.
-Morten :-)
-----Original Message-----
From: Shah Rupesh
[mailto:oracledba-ezmlmshield-x79526960.[Email address protected]
Sent: 7. oktober 2004 14:48
To: LazyDBA Discussion
Subject: RE: Sysdate behaves weird ...
select to_char(sysdate, 'MM-DD-YY HH24:MM:SS') from dual
is fine if you want time upto seconds.
What is the notation for milliseconds?
-----Original Message-----
From: Mike Sortino
[mailto:oracledba-ezmlmshield-x43880412.[Email address protected]
Sent: Wednesday, September 15, 2004 4:37 PM
To: LazyDBA Discussion
Subject: Re: Sysdate behaves weird ...
that is because MM is month. you need to use MI for minutes.
Mike
Juergen Stegmair wrote:
>Hi Gurus!
>
>Having 817 on AIX, I am getting the following results for sysdate
>queries:
>
>09-14-04 20:09:57
>09-14-04 20:09:59
>09-14-04 20:09:00
>09-14-04 20:09:00
>09-14-04 20:09:02
>09-14-04 20:09:03
>
>(and so on, the results always being 20:09:59, and then switching over
>again to 20:09:00)
>A little while later, I got the results switched one hour forward, but
>still with the MM staying the same:
>09-14-04 21:09:05
>09-14-04 21:09:06
>
>The full query being:
>
>select to_char(sysdate, 'MM-DD-YY HH24:MM:SS') from dual
>
>Any ideas? Bug? The system (O/S) date is ok. Basically, the minutes
>aren't couting correctly ...?
>
>Thanks,
>Juergen Stegmair
>++++++CONFIDENTIALITY NOTICE++++++
>The information in this email may be confidential and/or privileged.
This
email is intended to be reviewed by only the individual or organization
named above. If you are not the intended recipient or an authorized
representative of the intended recipient, you are hereby notified that
any
review, dissemination or copying of this email and its attachments, if
any,
or the information contained herein is prohibited. If you have received
this
email in error, please immediately notify the sender by return email and
delete this email from your system.
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>
>
>
>
>
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
This message contains information that may be privileged or confidential
and is the property of the Capgemini Group. It is intended only for the
person to whom it is addressed. If you are not the intended recipient,
you are not authorized to read, print, retain, copy, disseminate,
distribute, or use this message or any part thereof. If you receive this
message in error, please notify the sender immediately and delete all
copies of this message.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
**********************************************************************
This footnote confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
Questions can be forwarded to [Email address protected]
**********************************************************************
Oracle LazyDBA home page