Re: Challenge ! Find a query doing...

From: Vladimir Ivanovich <vladimiri_at_bosmedtech.com>
Date: Wed, 5 Sep 2001 15:00:35 -0400
Message-ID: <OuOl7.6$9j.2129@news.nyc.globix.net>

small correction:

vovka:scott> set scan on
vovka:scott> set serveroutput on
vovka:scott> declare

  2 D1 date;
  3 D2 date;
  4 begin
  5 D1 := to_date('01/01/2001','DD/MM/YYYY');   6 D2 := to_date('01/06/2001','DD/MM/YYYY');

  7  dbms_output.put_line('YYYY MM');
  8  For l in 0..round(months_between( D2, D1)) Loop
  9  dbms_output.put_line(to_char(D1+(l*30),'YYYY MM'));

 10 End Loop;
 11 End;
 12 /
YYYY MM
2001 01
2001 01
2001 03
2001 04
2001 05
2001 05

PL/SQL procedure successfully completed.

Notice the duplicate months?

(l*31) corrects the moths calculation:

vovka:scott> set scan on
vovka:scott> set serveroutput on
vovka:scott> declare

  2 D1 date;
  3 D2 date;
  4 begin
  5 D1 := to_date('01/01/2001','DD/MM/YYYY');   6 D2 := to_date('01/06/2001','DD/MM/YYYY');

  7  dbms_output.put_line('YYYY MM');
  8  For l in 0..round(months_between( D2, D1)) Loop
  9  dbms_output.put_line(to_char(D1+(l*31),'YYYY MM'));

 10 End Loop;
 11 End;
 12 /
YYYY MM
2001 01
2001 02
2001 03
2001 04
2001 05
2001 06

PL/SQL procedure successfully completed.

HTH, Vladimir
"cf" <news_at_sitinfo.com> wrote in message news:9n0635$h9j$1_at_reader1.fr.uu.net...
> Plus proprement.
>
> set scan on
> set serveroutput on
> declare
> D1 date;
> D2 date;
> begin
> D1 := to_date('&D1','DD/MM/YYYY');
> D2 := to_date('&D2','DD/MM/YYYY');
> For l in 0..round(months_between( D2, D1)) Loop
> dbms_output.put_line(to_char(D1+(l*30),'YYYY MM'));
> End Loop;
> End;
> /
>
> "Laurence TERRAS" <laurence.terras_at_digitech.fr> a écrit dans le message
> news: 9n0026$79j$1_at_d03.completel.fr...
> > Hello !
> >
> >
> > I'd like to fing a query wich be able to give Years and Months between 2
> > dates.
> > For example.
> > D1 = '22/09/2001' (DD/MM/YYYY)
> > D2 = '15/05/2002' (DD/MM/YYYY)
> >
> > results...
> >
> >
> >
>
>
Received on Wed Sep 05 2001 - 14:00:35 CDT