How to get time from Oracle?
Jimmy
Hello all,
I want to get system time from the Oracle. I use "select
to_char(sysdate, 'HH:MM:SS') from dual" in the SQL*PLUS. The first
result was "02:02:50". Later, I typed the above command again and the
result was "02:02:55". However, when I typed the command a few second
later, the result was "02:02:02". Why the system time seems running in
cycle? Also, is the sysdate function get time from the UNIX OS? (Oracle
is running on UNIX) If I want to change the system time in Oracle, how
can I do that?
THanks,
Jimmy
Steve Phelan
Er, read the manual.
Try a format mask of 'HH:MI:SS', then you'll get 'minutes' not 'months'.
Steve Phelan.
Jimmy wrote in message <34ECFD...@comp.polyu.edu.hk>...
Jagjot Singh Anand
USE 'MI' instead of 'MM'
Jagjot
Adrian G. Klingel
Jimmy,
You've got the format wrong. 'MM' stands for a 2 digit month. You'll
notice that February is the second month of the year, so '02' is coming
back. Change 'MM' to 'MI'. MI is the correct format for minutes in
Oracle. This is a common coding mistake, since it does not return an
Oracle error. Hope this helps.
Adrian Klingel
Consultant
David J. Rubin
Hi Jimmy,
You may have made a mistake that happens to many users... typing MM for the
minutes field by accident instead of MI. This would explain the 02:02:02.
Oracle gets its time from the OS on startup of the database. To change the
time in Oracle, change the time on the server, but be careful! There are
many timestamps saved throughout the database and if you backup the time
before the last timestamps you could get into trouble. Before changing the
time, record what the current bad time is... change the time in Unix and
wait until the bad time has passed.
Hope this helps!
David J. Rubin
Data Futures, Inc.
Yves Raymaekers
MM stands for month, use MI for minutes
Robert Marz
> to_char(sysdate, 'HH:MM:SS') from dual" in the SQL*PLUS. The first
> result was "02:02:50". Later, I typed the above command again and the
> result was "02:02:55". However, when I typed the command a few second
> later, the result was "02:02:02". Why the system time seems running in
> cycle? Also, is the sysdate function get time from the UNIX OS? (Oracle
> is running on UNIX) If I want to change the system time in Oracle, how
> can I do that?
Your system time is not running in a circle.
The MM-Format-String refers to Month (should change every 28 to 31 days :-)
Try MI instead of MM - it should work!
A complete Time-Stamp from Oracle could look like "YYYY-MON-DD HH24:MI:SS"
HTH
Robert
Steve Jelfs
Jimmy wrote:
>
> Hello all,
>
> I want to get system time from the Oracle. I use "select
> result was "02:02:50". Later, I typed the above command again and the
> result was "02:02:55". However, when I typed the command a few second
> later, the result was "02:02:02". Why the system time seems running in
> cycle?
This is, in fact, Oracles solution to the millenium problem. By making
the clock cycle backwards, developers will have until the year 1900 to
put things right. At a stroke an additional 96 years of inflated wage
rates and guaranteed jobs to work on the millenium "time-bomb"!!! ;-)
Sj
mbo...@umi.com
Heheheh! I had this very same problem years ago when I started programming
Oracle! I thought I was gonna go nuts!! I just couldn't understand it for
the life of me! ;-)
-Mark J. Bobak
Oracle Developer, UMI
In article <887891871.3125.0...@news.demon.co.uk>,
"Steve Phelan" <ste...@XXnospamXX.toneline.demon.co.uk> wrote:
>
> Er, read the manual.
>
> Try a format mask of 'HH:MI:SS', then you'll get 'minutes' not 'months'.
>
> Steve Phelan.
>
> Jimmy wrote in message <34ECFD...@comp.polyu.edu.hk>...
> >Hello all,
> >
> > I want to get system time from the Oracle. I use "select
> >to_char(sysdate, 'HH:MM:SS') from dual" in the SQL*PLUS. The first
> >result was "02:02:50". Later, I typed the above command again and the
> >result was "02:02:55". However, when I typed the command a few second
> >later, the result was "02:02:02". Why the system time seems running in
> >cycle? Also, is the sysdate function get time from the UNIX OS? (Oracle
> >is running on UNIX) If I want to change the system time in Oracle, how
> >can I do that?
> >
> >THanks,
> >Jimmy
>
>
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading
AMARENDRA B NETTEM
Hi,
You are giving the wrong format for minutes (it should me MI not MM, MM is
for
month)
Try TO_CHAR(SYSDATE, 'HH:MI:SS')
AMARENDRA
Steve Jelfs wrote:
> Jimmy wrote:
> >
> > Hello all,
> >
> > I want to get system time from the Oracle. I use "select
> > to_char(sysdate, 'HH:MM:SS') from dual" in the SQL*PLUS. The first
> > result was "02:02:50". Later, I typed the above command again and the
> > result was "02:02:55". However, when I typed the command a few second
> > later, the result was "02:02:02". Why the system time seems running in
> > cycle?
>
> This is, in fact, Oracles solution to the millenium problem. By making
> the clock cycle backwards, developers will have until the year 1900 to
> put things right. At a stroke an additional 96 years of inflated wage
> rates and guaranteed jobs to work on the millenium "time-bomb"!!! ;-)
>
> Sj
--
*****************************************************************
AMARENDRA B NETTEM
Oracle Certified DBA (OCP)
Whittman-Hart Inc.,
311 South Wacker Drive, Suite 3500
Chicago, IL 60606.
Residence:
-----------
5039 N E River Road,
Apt. 1A
NORRIDGE, IL 60656
Ph.No. (708) 583 9870 (H)
(312) 913 6758 (W)
E-mail:net...@charlie.cns.iit.edu,
ane...@whittman-hart.com
Homepage: http://www.iit.edu/~nettama
****************************************************************
Opinions are mine and do not necessarily reflect those
of Whittman-hart Inc.