Re: question about subtracting dates

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

You have hidden details we need to know; your default date format is 'DD-MON-YY' or something similar. Passing your dates through the to_date() function with that date mask is the problem:

SQL> insert all
  2 into ord1type_j
  3 values (to_date('1994-11-15 00:00:00', 'RRRR-MM-DD HH24:MI:SS'),   4 to_date('2094-06-11 00:00:00', 'RRRR-MM-DD HH24:MI:SS'))   5 select * from dual;

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter session set nls_date_Format = 'RRRR-MM-DD';

Session altered.

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

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

SQL>
SQL> alter session set nls_date_Format = 'YY-MM-DD';

Session altered.

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

       157

SQL> Your to_date() call, using the default date format, is causing your 1994 record to return the year 2094, and thus your erroneous answer. In any event the year for both records, during the processing, is 2094.

David Fitzjarrell Received on Fri Sep 12 2008 - 15:23:18 CDT