Re: question about subtracting dates

Comments embedded.
On Sep 12, 2:32 pm, Adam Sandler <cor..._at_excite.com> wrote:
> On Sep 12, 9:48 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> 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.

> SELECT
>   TO_DATE('1994-11-15 00:00:00') - TO_DATE('2094-06-11 00:00:00')
> FROM
>   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