Get sysdate as number

[klauss...@my-deja.com]

I want to get the absoulute oracle time sysdate as a number.
I tried
select to_number(sysdate) from dual

But I always got a error.

Any ideas?

Thanks in advance

Klaus

Sent via Deja.com http://www.deja.com/
Before you buy.

[Patrick Diks]

Hi,

You need a reference date to count from ie:
SQL> select sysdate - to_date('01-01-1970','dd-mm-yyyy') from dual;

SYSDATE-TO_DATE('01-01-1970','DD-MM-YYYY')
------------------------------------------
11269,588

The resulting number is the difference in days.

HTH,

Patrick Diks

[Mark D Powell]

In article <3A09514E...@capgemini.nl>,

Patrick gave the response that I think Klaus can use, but since did not
specifiy what type of number or what he wanted it for he may want the
Oracle Julian date which you can get using:

UT1> l
1* select to_char(sysdate,'J') "Julian" from sys.dual
UT1> /

Julian
-------
2451857

--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --

[Anton Dischner]

Hi Klaus,

what you are looking for is the Julian calendar, started Dec 31, 4713 BC.

Try select to_char(sysdate, 'J') from dual;

regards,

Toni

> I want to get the absoulute oracle time sysdate as a number.

--
ZXR750H, 55 Mm.
Q: How did the medical community come up with the term "PMS"?
A: "Mad Cow Disease" was already taken.
Posen fuer Anfaenger: http://www.w-klch.med.uni-muenchen.de/dischner

[Frank van Bortel]

But be careful with the results: according to the Julian
calendar implementation in Oracle, the year 0 exists
(and is a leap year!)

Mark D Powell wrote:

> In article <3A09514E...@capgemini.nl>,
> Patrick Diks <patric...@capgemini.nl> wrote:
> > Hi,
> >
> > You need a reference date to count from ie:
> > SQL> select sysdate - to_date('01-01-1970','dd-mm-yyyy') from dual;
> >
> > SYSDATE-TO_DATE('01-01-1970','DD-MM-YYYY')
> > ------------------------------------------
> > 11269,588
> >
> > The resulting number is the difference in days.
> >
> > HTH,
> >
> > Patrick Diks
> >
> > klauss...@my-deja.com wrote:
> > >

> > > I want to get the absoulute oracle time sysdate as a number.

> > > I tried
> > > select to_number(sysdate) from dual
> > >
> > > But I always got a error.
> > >
> > > Any ideas?
> > >
> > > Thanks in advance
> > >
> > > Klaus
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> Patrick gave the response that I think Klaus can use, but since did not
> specifiy what type of number or what he wanted it for he may want the
> Oracle Julian date which you can get using:
>
> UT1> l
> 1* select to_char(sysdate,'J') "Julian" from sys.dual
> UT1> /
>
> Julian
> -------
> 2451857
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
Gtrz,

Frank van Bortel

[David Fitzjarrell]

In our last gripping episode Frank van Bortel <fbo...@home.nl> wrote:
> But be careful with the results: according to the Julian
> calendar implementation in Oracle, the year 0 exists
> (and is a leap year!)
>

Which version of Oracle? As I'm temporarily without an 8i instance I
cannot check that, but on 7.3.4.4 and 8.0.5 the Julian year 0 does not
exist:

select to_date(0,'J')
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484

--
David Fitzjarrell
Oracle Certified DBA

[Van Messner]

Am I missing something? If you started counting in BC and moved forward to
AD there should be a year zero, shouldn't there?

Van

"Anton Dischner" <disc...@klch.med.uni-muenchen.de> wrote in message
news:081120001504586067%disc...@klch.med.uni-muenchen.de...

> Hi Klaus,
>
> what you are looking for is the Julian calendar, started Dec 31, 4713 BC.
>
> Try select to_char(sysdate, 'J') from dual;
>
> regards,
>
> Toni
>

> > I want to get the absoulute oracle time sysdate as a number.
>

[Howard J. Rogers]

Not calendrically, no.

There was no year zero. AD means 'In the year of the lord', so before he
was born, it was 1 BC ('cos he wasn't there yet, and hence it was a 'before
Christ' year). Once he was born, however, the year belonged to him, and
hence was the first year of the lord, and thus 1AD.

Naturally, Christ was actually born in 7BC which rather stuffs things up,
but you can thank Denis the Little (more properly known as Dionysius
Exiguus) for that slight miscaluclation.

<Don anti-flame suit>

For this reason, the third millennium doesn't start until January 1st 2001.
But hey -it was a great party this year, whatever!

Now, how Oracle takes all that into account, I haven't a clue. I can't
stand working with Oracle dates at the best of times, but if it ever
displays a year zero, it's wrong.

Incidentally, if you are as vaguely interested in calendars and stuff as me,
take a trip to http://www.pauahtun.org/CalendarFAQ/cal/calendar23.html

Regards
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources: http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------

"Van Messner" <vmes...@bestweb.net> wrote in message
news:3anO5.81$s83....@monger.newsread.com...

[jamesn...@my-deja.com]

This reminds me of a 'bug' I was forwarded. Someone at DEC was obviously
bored.
SOFTWARE PROBLEM REPORT

------------------------------------------------------------------------
------
SPR NUMBER: 11-60903
OPERATING SYSTEM: VAX/VMS
O.S. VERSION: V3.2
COMPONENT: Run-Time Library
SUB-COMPONENT: LIB$ routines
DATE ANSWERED: 13-Oct-1983
MAINTAINER: ----------------
SPR PROBLEM ABSTRACT: User claims year 2000 should not
be a leap year.

--- --- --- --- --- --- ---
| D | I | G | I | T | A | L |
--- --- --- --- --- --- ---

SPR ANSWER FORM

SPR NO. 11-60903

SYSTEM VERSION PRODUCT VERSION COMPONENT
SOFTWARE: VAX/VMS V3.2 VAX/VMS V3.2 Run-Time Library

PROBLEM:

The LIB$DAY Run-Time Library service "incorrectly" assumes the year
2000 is a leap year.

RESPONSE:

Thank you for your forward-looking SPR.

Various system services, such as SYS$ASCTIM assume that the year 2000
will be a leap year. Although one can never be sure of what will
happen at some future time, there is strong historical precedent for
presuming that the present Gregorian calendar will still be in affect
by the year 2000. Since we also hope that VMS will still be around by
then, we have chosen to adhere to these precedents.

The purpose of a calendar is to reckon time in advance, to show how
many days have to elapse until a certain event takes place in the
future, such as the harvest or the release of VMS V4. The earliest
calendars, naturally, were crude and tended to be based upon
the seasons or the lunar cycle.

The calendar of the Assyrians, for example, was based upon the phases
of the moon. They knew that a lunation (the time from one full moon
to the next) was 29 1/2 days long, so their lunar year had a duration
of 354 days. This fell short of the solar year by about 11 days.
(The exact time for the solar year is approximately 365 days, 5 hours,
48 minutes, and 46 seconds.) After 3 years, such a lunar calendar
would be off by a whole month, so the Assyrians added an extra month
from time to time to keep their calendar in synchronization with the
seasons.

The best approximation that was possible in antiquity was a 19-year
period, with 7 of these 19 years having 13 months (leap months). This
scheme was adopted as the basis for the religious calendar used by the
Jews. (The Arabs also used this calendar until Mohammed forbade
shifting from 12 months to 13 months.)

When Rome emerged as a world power, the difficulties of making a
calendar were well known, but the Romans complicated their lives
because of their superstition that even numbers were unlucky. Hence
their months were 29 or 31 days long, with the exception of February,
which had 28 days. Every second year, the Roman calendar included an
extra month called Mercedonius of 22 or 23 days to keep up with the
solar year.

Even this algorithm was very poor, so that in 45 BC, Caesar, advised
by the astronomer Sosigenes, ordered a sweeping reform. By imperial
decree, one year was made 445 days long to bring the calendar back in
step with the seasons. The new calendar, similar to the one we now
use was called the Julian calendar (named after Julius Caesar). It's
months were 30 or 31 days in length and every fourth year was made a
leap year (having 366 days). Caesar also decreed that the year would
start with the first of January, not the vernal equinox in late March.

Caesar's year was 11 1/2 minutes short of the calculations recommended
by Sosigenes and eventually the date of the vernal equinox began to
drift. Roger Bacon became alarmed and sent a note to Pope Clement IV,
who apparently was not impressed. Pope Sixtus IV later became
convinced that another reform was needed and called the German
astronomer, Regiomontanus, to Rome to advise him. Unfortunately,
Regiomontanus died of the plague shortly thereafter and the plans died
as well.

In 1545, the Council of Trent authorized Pope Gregory XIII to reform
the calendar once more. Most of the mathematical work was done by
Father Christopher Clavius, S.J. The immediate correction that was
adopted was that Thursday, October 4, 1582 was to be the last day of
the Julian calendar. The next day was Friday, with the date of
October 15. For long range accuracy, a formula suggested by the
Vatican librarian Aloysius Giglio was adopted. It said that every
fourth year is a leap year except for century years that are not
divisible by 400. Thus 1700, 1800 and 1900 would not be leap years,
but 2000 would be a leap year since 2000 is divisible by 400. This
rule eliminates 3 leap years every 4 centuries, making the calendar
sufficiently correct for most ordinary purposes. This calendar is
known as the Gregorian calendar and is the one that we now use today.
(It is interesting to note that in 1582, all the Protestant princes
ignored the papal decree and so many countries continued to use the
Julian calendar until either 1698 or 1752. In Russia, it needed the
revolution to introduce the Gregorian calendar in 1918.)

This explains why VMS chooses to treat the year 2000 as a leap year.

Despite the great accuracy of the Gregorian calendar, it still falls
behind very slightly every few years. If you are very concerned about
this problem, we suggest that you tune in short wave radio station
WWV, which broadcasts official time signals for use in the United
States. About once every 3 years, they declare a leap second at which
time you should be careful to adjust your system clock. If you have
trouble picking up their signals, we suggest you purchase an atomic
clock (not manufactured by Digital and not a VAX option at this time).

END OF SPR RESPONSE
In article <3a0a...@news.iprimus.com.au>,

[Frank van Bortel]

You're right - I should have chosen my words better:
the Georgian (I believe is the phrase) year 0 does not exist,
unless you use Julian date arithmatic:
Dec, 31st, 1BC would be day 1721057 , whereas day 1721424
would be Jan, 1st, 1 AD - according to our calendar, two
consecutive days: new years eve and new year.
However, there are 366 days inbetween those two dates, according
to the Julian calendar. So, the year 0 must exist, and have 366 days,
making it a leap year.

Comments are welcome.

David Fitzjarrell wrote:

> > > > > I want to get the absoulute oracle time sysdate as a number.

> > > > > I tried
> > > > > select to_number(sysdate) from dual
> > > > >
> > > > > But I always got a error.
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > > Klaus
> > > > >

> > > > > Sent via Deja.com http://www.deja.com/
> > > > > Before you buy.

> > > Patrick gave the response that I think Klaus can use, but since did
> not
> > > specifiy what type of number or what he wanted it for he may want
> the
> > > Oracle Julian date which you can get using:
> > >
> > > UT1> l
> > > 1* select to_char(sysdate,'J') "Julian" from sys.dual
> > > UT1> /
> > >
> > > Julian
> > > -------
> > > 2451857
> > >
> > > --
> > > Mark D. Powell -- The only advice that counts is the advice that
> > > you follow so follow your own advice --
> > >

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >

> > --
> > Gtrz,
> >
> > Frank van Bortel
> >
> >
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
Gtrz,

Frank van Bortel

[Frank van Bortel]

Gregorian that is, not georgian.