Re: question about subtracting dates

From: <fitzjarrell_at_cox.net>
Date: Fri, 12 Sep 2008 13:02:10 -0700 (PDT)
Message-ID: <587023c9-29a0-4f5c-ba54-5b2e4431ece8@m73g2000hsh.googlegroups.com>


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?
>
> 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.
>
> > 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.
>
> 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

Were you truly to return that one record, and only that one record, by your query the result would be as you expect. But your query does NOT return values from one, and only one, record:

SQL> SELECT
  2 max(RPR_START_TIME)
  3 from
  4 ORD1TYPE_J; MAX(RPR_START_TIME)



2094-11-15 00:00:00

SQL>
SQL> SELECT
  2 max(JCN_DATE)
  3 from
  4 ORD1TYPE_J; MAX(JCN_DATE)



2094-06-11 00:00:00

SQL>
SQL> SELECT
  2 TO_DATE(max(RPR_START_TIME)) - -- Returns MAX(RPR_START_TIME) from all records in the table
  3 TO_DATE(max(JCN_DATE)) AS formula1 -- Returns MAX(JCN_DATE) from all records in the table and subtracts them   4 FROM
  5 ORD1TYPE_J;   FORMULA1


       157

The result from the above query is consistent with the results you have already posted, and you have shown no manipulation of the date values prior to the subtraction, therefore the year and time are included in the subtracted date values and, as illustrated, the year portion for both values is 2094.

IF your query was returning the values you claim this would be the result:

SQL>
SQL> SELECT
  2 max(RPR_START_TIME)
  3 from
  4 ORD1TYPE_J; MAX(RPR_START_TIME)



1994-11-15 00:00:00

SQL>
SQL> SELECT
  2 max(JCN_DATE)
  3 from
  4 ORD1TYPE_J; MAX(JCN_DATE)



2094-06-11 00:00:00

SQL>
SQL> SELECT
  2 TO_DATE(max(RPR_START_TIME)) - -- Returns MAX(RPR_START_TIME) from the single record in the table
  3 TO_DATE(max(JCN_DATE)) AS formula1 -- Returns MAX(JCN_DATE) from the single record in the table and subtracts them   4 FROM
  5 ORD1TYPE_J;   FORMULA1


    -36368

And this result is not consistent with the posted data you've provided. Therefore your assumptions must be in error.

David Fitzjarrell Received on Fri Sep 12 2008 - 15:02:10 CDT

Original text of this message