Re: date arithmetic in oracle?

In article <Pine.OSF.4.21.0004302022030.2569-100000_at_wpi.WPI.EDU>,   Peter Connolly <peter_at_wpi.edu> wrote:
> Oracle stores all dates as Julian dates. This means that Oracle
 stores a
> day as something like the number 123456789.1234. Where 123456789 is
 the
> number of days since epoch (The day that Oracle considers the
 beginning of
> time). The decimal part (.1234) reflects the time of day as a
 fraction of
> a whole day.
>

no, dates are stored in a 7 byte internal format. it is not julian or anything like that. We store the century (eg: 19, 20), the year (eg: 00, 99), the month (1-12), the day of the month (1-31), the hour, minutes and seconds.

see http://technet.oracle.com/doc/server73x/OCI73/ch3.htm#?cUFi2e3jdru

for the exact 7 byte format and a brief discussion of the storage.

The rest, add 1 to get tomorrow, add .5 to get 12 hours from now and so on, applies -- but the date format is definitely not a julian style date at all....

> To add a day you just add one :
>
> SELECT sysdate+1
> FROM dual;
>
> To add a week you add 7:
>
> SELECT sysdate+7
> FROM dual;
>
> To add 12 hours you add .5:
>
> SELECT sysdate+.5
> FROM dual;
>
> -Peter
>
> On Sun, 30 Apr 2000, bmt wrote:
>
> > I want to select on the basis of date arithmetic.. ie
> >
> > create table TABLEX (A varchar(10), B date);
> > insert into tablex values ('number1', ( to_date ( '10-Jan-2000',
> > 'dd-Mon-yyyy','NLS_DATE_LANGUAGE = American') ));
> > insert into tablex values ('number2', ( to_date ( '20-Jan-2000',
> > 'dd-Mon-yyyy','NLS_DATE_LANGUAGE = American') ));
> >
> >
> > and I want to select * from TABLEX where date < (sysdate-7days)
> > any ideas??
> >
> > I know I can do
> > select * from tablex where b < (select sysdate from dual); to get
 where
> > date < sysdate.
> >
> > and can do
> > select * from tablex where b < (SELECT ADD_MONTHS(SYSDATE, -1) FROM
> > DUAL);
> > to get where date < sysdate - 1 month.
> >
> > As far as I can tell there is no add_days or add_weeks function. Any
> > ideas??
> >
> > M
> > ===================================================================
> > Matthew Taylor s176226_at_student.uq.edu.au
> > ph 3202 6927/3202 9040/0417 833 021 bmatthewtaylor_at_hotmail.com
> > University of Queensland, St Lucia, Brisbane, Australia.
> > ===================================================================
> >
> >
> >
>
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


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