Re: date arithmetic

["Johnson, Dan" <Dan_Johnson@WRIGHTEXPRESS.COM>]

You can't subtract one date from another. What exactly are you looking for?
There are ways to calculate the number of months/day/years between dates.

for months use:

trunc(months_between(date1,date2))

for days use:

mod(months_between(date1,date2)-trunc(months_between(date1,date2)),31)*31

Hope this helps,
Dan

-----Original Message-----
From: Turner, Ivan
Sent: Thursday, January 10, 2002 12:51 PM
To:
Subject: date arithmetic


Any suggestions or better yet, any solutions.

select program_name_l, table_name_l,
to_char(date_time_stamp_l,'YYYYMMDDHHSS'),
to_char(run_date_l,'YYYYMMDDHHSS'),
to_date(run_date_l - date_time_stamp_l,'HHSS')
from PS_AR_INTR_EXC_L
where program_name_l in ('ARL9400','ARL9100', 'ARL9000', 'ARL9200',
'ARL9300','ARL0020','ARL9500')
and date_time_stamp_l > '30-NOV-01'
and run_date_l > '30-NOV-01'
and table_name_l in ('RESDB','LATIS')
and rownum < 20
order by program_name_l, run_Date_l;


ERROR at line 4:
ORA-01858: a non-numeric character was found where a numeric was expected
4  to_date(run_date_l - date_time_stamp_l,'HHSS')
                                *