Re: question about subtracting dates

Comments embedded.
On Sep 11, 5:27 pm, Adam Sandler <cor..._at_excite.com> wrote:
> Hello,
>
> I'm using 10.2.0.1.0
>
> I'm maintaining app which executes this SQL:
>
> SELECT
>   TO_DATE(max(RPR_START_TIME)) - TO_DATE(max(JCN_DATE)) AS formula1
> FROM
>   [snip]
>

Which is a problem unto itself as you're converting DATE values to ... DATE values, which is needless work.

> Here's some supporting information:
>
> DESC ORD1TYPE_J;
>
> Name                           Null     Type
> ------------------------------ -------- ------------------------
> RPR_START_TIME                          DATE
> JCN_DATE                       DATE
>
> At any rate, there's a row where:
>
> RPR_START_TIME = 1994-11-15 00:00:00.0
> JCN_DATE  = 2094-06-11 00:00:00.0
> formula1 = 157
>

But you're not processing 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:

SQL> create table ord1type_j(

  2          rpr_start_time date,
  3          jcn_date        date

Table created.

SQL>
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 into ord1type_j
  6 values (to_date('2094-11-15 00:00:00', 'RRRR-MM-DD HH24:MI:SS'),   7 to_date('2094-05-17 00:00:00', 'RRRR-MM-DD HH24:MI:SS'))   8 select * from dual;

2 rows created.

SQL>
SQL> SELECT
  2 TO_DATE(max(RPR_START_TIME)) - TO_DATE(max(JCN_DATE)) AS formula1
  3 FROM
  4 ORD1TYPE_J;   FORMULA1

       157

SQL>
SQL> SELECT
  2 RPR_START_TIME - JCN_DATE AS formula1   3 FROM
  4 ORD1TYPE_J;   FORMULA1

    -36368

       182

SQL>
SQL> SELECT
  2 abs(RPR_START_TIME - JCN_DATE) AS formula1   3 FROM
  4 ORD1TYPE_J;   FORMULA1

     36368
       182

SQL>
> Now, November 15 is day 315 and June 11 is day 162; 319 - 162 = 157...
> which matches my formula1 value.  But what perplexes me is the year
> for JCN_DATE is 2094.  Why didn't that get included in the
> calculation?  

It did; see above, as you have an RPR_START_TIME which is 157 days greater than the 'suspect' JCN_DATE in your table data. Again, you're not processing a row, you're processing discrete values from the entire table.

> The value for the difference in dates should be much
> larger than 157 if one of the years is 2094.
>

But not if BOTH years are 2094. Again, see above.

> Any suggestions are greatly appreciated.
>

Check your table data; the calculation is most likely correct and your assumptions are off.

> Thanks!

David Fitzjarrell Received on Fri Sep 12 2008 - 10:48:39 CDT