Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    5

    Unanswered: Dates and Loops question

    I have a table called Months_Days and I want to fill it with the months 1 thru 12 in the months column, and fill in the corresponding number of days in the days column. i only want to use 1 loop and 1 Insert Into statement. Here's what i have so far. i can get the months inserted, but it inserts 31 for the number of days for each month. can anybody see what i'm doing wrong?
    thanks in advance!


    Drop Table Month_Days;
    Create Table Month_Days(
    Month Number(2),
    Days Number(2));


    Declare
    LoopM Binary_Integer;
    LoopD Binary_Integer;
    Begin_Date Date;
    End_Date Date;
    Begin
    LoopM:= 0;
    LoopD:= 0;

    Loop
    Begin_Date:= To_Date('01-Jan-2008', 'DD, Mon, YYYY');
    End_Date:= Last_Day(Begin_Date);
    LoopM:=LoopM+1;
    LoopD:=End_Date-Begin_Date+1;
    IF LoopM=13 Then
    Exit;
    End IF;
    End_Date:= Add_Months(Begin_Date, 1);
    Insert Into Month_Days Values (LoopM, LoopD);
    End Loop;
    End;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Dates and Loops question

    Yes: every time you go round the loop you reset Begin_Date to 01-Jan-2008, so you always set LoopD to the number of days in January.

    Your code is way over-complicated, and doesn't make use of basic PL/SQL constructs like the FOR loop, e.g.

    FOR LoopM IN 1..12 LOOP
    ...
    END LOOP;

    Here is a working version of your code:

    Code:
    Declare
      LoopD Binary_Integer;
      Begin_Date Date;
      End_Date Date;
    Begin
      Begin_Date:= To_Date('01-Jan-2008', 'DD, Mon, YYYY');
      FOR LoopM IN 1..12
      Loop
        End_Date:= Last_Day(Begin_Date);
        LoopD:=End_Date-Begin_Date+1;
        Insert Into Month_Days Values (LoopM, LoopD);
        Begin_Date:= Add_Months(Begin_Date, 1);
      End Loop;
    End;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •