Re: date arithmetic

[Don Mellen <donm@ONTKO.COM>]

I'm not sure why you're ignoring the minutes in your dates, but
regardless, when you subtract 2 dates, the result is a number of days
between them, which you are then attempting a "to_date" on.

If you have SQR 4.0 or later, you can do date arithmetic on the 2 dates
you pull out (by pulling them as dates preferably) and possibly declaring
some date variables for interim calcs.

If you have an earlier SQR, or if you "have" to pull out the value as a
column of the select for whatever reason, you might try something like...

replace(
to_char(trunc((run_date_l - date_time_stamp_l) * 24, 0),'999') ||
':' ||
to_char(trunc( ( ((run_date_l - date_time_stamp_l) * 24) -
   trunc((run_date_l - date_time_stamp_l) * 24, 0) ) * 60, 0),'09') ||
':' ||
to_char(trunc( ( (((run_date_l - date_time_stamp_l) * 24) * 60) -
   trunc(((run_date_l - date_time_stamp_l) * 24) * 60, 0) ) * 60, 0),'09')
,' ','')

Which woud give you the difference in HH24:MI:SS format (for example,
switch to whatever format you need). If you know the dates to be less than
24 hours apart, you can switch to the simpler....

to_char(trunc(sydate) + (run_date_l - date_time_stamp_l),'HH24:MI:SS')

HTH,
Don

On Thu, 10 Jan 2002, Turner, Ivan wrote:

> 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')
>                                 *
>

-----------------------------------------------------------------------
Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
donm@ontko.com |  "In the beginning, there was nothing, which exploded"