Re: PL/SQL Date Arithmetic not working correctly!!

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/03/02
Message-ID: <89lvj9$jpj$1@nnrp1.deja.com>#1/1

In article <89lti7$ia8$1_at_nnrp1.deja.com>,   newopt_at_my-deja.com wrote:
> I do date arithmetic in the script that follows, but it is not
> calculating the correct number of days between 2 days?
> If SYSDATE and v_tgttstmp are both in the same month it works fine
> though?
>
> I set the date format with the following statement in my 1st line of
> code:
> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
>
> For example: The following excerpt from my script subtarcts a date
> from a status table form SYSDATE:
> v_tgttstmp is type DATE
>
> -- Calculate Elapsed Days Difference
> v_days := SYSDATE - v_tgttstmp;
> dbms_output.put_line(sysdate || v_tgttstmp || v_days);
>
> Here's the output of the script when I run it using
> DBMS_output.put_line:
> As you can see below SYSDATE = 2000 03 02, v_tgttstmp = 2000 02 28 and
> v_days = 2 when it should be 3?
>
> SQL> @vantagestatus.sql
> Session altered.
> 2000 03 022000 02 282
> 2000 03 022000 02 282
> PL/SQL procedure successfully completed.
>
> Here's the entire script:
>

What time is it?

ops$tkyte_at_8i> declare

  2          l_date1 date default sysdate;
  3          l_date2 date default to_date( '28-feb-2000 23:59:59', 'dd-

mon-yyyy hh24:mi:ss' );
  4 begin

  5          dbms_output.put_line( to_char( l_date1, 'dd mon yyyy' ) );
  6          dbms_output.put_line( to_char( l_date2, 'dd mon yyyy' ) );
  7          dbms_output.put_line( l_date1-l_date2 );

  8 end;
  9 /
02 mar 2000
28 feb 2000
2.41771990740740740740740740740740740741

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> declare

  2          l_date1 date default sysdate;
  3          l_date2 date default to_date( '28-feb-2000 00:00:00', 'dd-

mon-yyyy hh24:mi:ss' );
  4 begin

  5          dbms_output.put_line( to_char( l_date1, 'dd mon yyyy' ) );
  6          dbms_output.put_line( to_char( l_date2, 'dd mon yyyy' ) );
  7          dbms_output.put_line( l_date1-l_date2 );

  8 end;
  9 /
02 mar 2000
28 feb 2000

3.41770833333333333333333333333333333333

PL/SQL procedure successfully completed.

Maybe you really want to TRUNC() the two dates to "normalize" them with respect to eachother and then see how many days between:

ops$tkyte_at_8i> declare

  2          l_date1 date default sysdate;
  3          l_date2 date default to_date( '28-feb-2000 00:00:00', 'dd-

mon-yyyy hh24:mi:ss' );
  4 begin

  5          dbms_output.put_line( to_char( l_date1, 'dd mon yyyy' ) );
  6          dbms_output.put_line( to_char( l_date2, 'dd mon yyyy' ) );
  7          dbms_output.put_line( trunc(l_date1)-trunc(l_date2) );

  8 end;
  9 /
02 mar 2000
28 feb 2000
3

PL/SQL procedure successfully completed.

> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
 [snip]
>
> Any help would be appreciated!!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Mar 02 2000 - 00:00:00 CST