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

Thanks Tom!!

Using TRUNC fixed the problem!

In article <89lvj9$jpj$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 02 2000 - 00:00:00 CST