Re: question about subtracting dates

From: <>
Date: Fri, 12 Sep 2008 12:46:24 -0700 (PDT)
Message-ID: <>

Comments embedded.
On Sep 12, 2:32 pm, Adam Sandler <> wrote:
> On Sep 12, 9:48 am, "" <> wrote:
> David, thanks for your reply... I'm a bit confused by what you
> wrote...
> > But you're not processing a row
> I never meant to imply that I'm processing across a row
> > you're processing the absolute
> > maximum RPR_START_TIME and JCN_DATE values from the given table which
> > may only be 157 days apart:
> Even if the where clause effectively filters the results returned down
> to one record?  One record with the date values I've posted here?

It doesn't, as the query result proves. You have far more than one record in that table so that the max(RPR_START_TIME) is not in the same record as the max(JCN_DATE).

> t's just that for one record, one date retrieved from the database is
> year 1994 and another one is 2094.  If I was truly subtracting those
> days, 1994 - 2094, the answer should be -36368.

It isn't subtracting those dates. I would modify that original query to return ONLY the MAX(RPR_START_TIME), then change it again to return only the MAX(JCN_DATE) and you'll see they are not from the same database record.

> > But not if BOTH years are 2094.  Again, see above.
> BOTH years are never 2094... for the result returned by the entire SQL
> statement it's 1994 minus 2094.

Sorry, no, it isn't, not from the query you've provided nor from the ONE record from hundreds or thousands you've chosen to report.

>   TO_DATE('1994-11-15 00:00:00') - TO_DATE('2094-06-11 00:00:00')
>   DUAL;
> TO_DATE(15-NOV-1994)-TO_DATE(11-JUN-2094)
> ---------------------------------------------------
> -36368

And that IS correct if those were the actual values used by the original query you posted. They are not; the result of 157 indicates there IS a record in this table with an RPR_START_TIME of '2094-11-15 00:00:00' and that the max(JCN_DATE) comes from the record you chose to post. Thus, both of the returned values are in 2094. Unless, of course, you didn't post the actual query but a lame misrepentation of it and you've hidden aspects we need to see.

David Fitzjarrell Received on Fri Sep 12 2008 - 14:46:24 CDT

Original text of this message