Re: date arithmetic in oracle?

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.

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.
> ===================================================================
>
>
>
Received on Sun Apr 30 2000 - 00:00:00 CDT