Code error in a SQL Server While-If Loop

Question

I know that there are other posts with code that solve my problem but I don't want to take another's code so I'm trying to do it by myself and I'm stuck with the month not increasing problem, so if anyone can help me with that mistake it will be awesome.

The problem is:

I have to populate the table Time from year 1990 to 2016 with all the months and days, I have already achieved that the code works and it populates correctly the years and the days but months increases to January (1) and then is not increasing so the table is filled with all months being January (LOL)

Here's my code:

create table Time
(
    Year int, 
    Month int,
    Day int
)

create procedure pTime
as
    declare @year int, @month int, @day int;
    set @year = 1990;
    set @month = 12;
    set @day = 10;

while(@year<=2016)
Begin

    If(@day = 29)
    Begin

        set @month = @month + 1;

        If(@month = 13)
        Begin

            set @month = 1;
            set @day = 1;
            set @year = @year + 1;

            insert into Time values (@year, @month, @day);
        End
    End 

    else
    Begin
        If(@day = 29)
        Begin

            set @month = @month + 1;
            set @day = 1;

            insert into Time values (@year, @month, @day);
        End

        Else    
        Begin

            insert into Time values (@year, @month, @day);

            set @day = @day + 1;
        End
    End
End

Any idea where is my mistake or any suggestion?

"Please debug my code" kind of questions are not particularly welcome here. I think your approach to solve the problem is not very efficient. I would rather use a date data type and the DateAdd function. - But as you want to pursue your own approach, take a long hard look at the If(@year = 29) condition. — PhilS
– PhilS, Commented Apr 17, 2016 at 20:32
Oh, I missed that. Let me correct it. Thanks :) I don't want the community to debug my code, I just looking for some hint of what I'm missing, that's all. — RalphVB
– RalphVB, Commented Apr 17, 2016 at 20:42

user212514 · Accepted Answer · 2016-04-17 23:50:53Z

2

I didn't look very closely for your mistake because SQL Server has some helpful date arithmetic functions. Here's simplified version of your stored procedure:

create procedure pTime
as
  declare @theDate date = '12/10/1990', @days int = 0

while @theDate < '1/1/2016'
  begin
    insert into Time (Year, Month, Day) values (datepart(year, @theDate), datepart(month, @theDate), datepart(day, @theDate));
    set @theDate = dateadd(day, 1, @theDate)
  end

answered Apr 17, 2016 at 23:50

user212514

3,1381 gold badge17 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RalphVB Over a year ago

You are a f@ckin' GOD. This is what I was talking about, not debugging my code, just giving me hints about how to do it. You are really awesome, dude. I'm going to study this code. Thanks a lot, dude :D

user212514 Over a year ago

Thanks for the praise and you're welcome. Feel free to ask if you have questions. Please accept the answer if it does work out for you.

Alan Burstein · Accepted Answer · 2016-04-18 03:02:58Z

2

Another faster approach would be to use a tally table. Note the code below:

WITH
E(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
         SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
         SELECT 1 UNION ALL SELECT 1),
iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 FROM E a,E b,E c,E d,E e),
dates(dt) AS 
(
  SELECT TOP(datediff(DAY,'19900101','20160101')) DATEADD(day,N,'19900101') 
  FROM iTally
)
--INSERT [time] --uncomment for the insert, leave commented to see what will be inserted
SELECT YEAR(dt), MONTH(dt), DAY(dt)
FROM dates;

answered Apr 18, 2016 at 3:02

Alan Burstein

8,0061 gold badge18 silver badges19 bronze badges

1 Comment

Eric Over a year ago

This is the fastest way compare with other answers

irakliG. · Accepted Answer · 2016-04-17 21:12:59Z

1

Why do you need If(@year = 29) condition? In your code this block never will be executed. try this:

create procedure pTime
as
    declare @year int, @month int, @day int;
    set @year = 1990;
    set @month = 12;
    set @day = 10;

while(@year<=2016)
Begin
    If(@day = 29)
    Begin

        set @month = @month + 1;
        set @day = 1;

        If(@month = 13)
        Begin
            set @month = 1;            
            set @year = @year + 1;
            insert into Time values (@year, @month, @day);
        End
    End 

    else
    Begin
        If(@day = 29)
        Begin

            set @month = @month + 1;
            set @day = 1;

            insert into Time values (@year, @month, @day);
        End

        Else    
        Begin

            insert into Time values (@year, @month, @day);

            set @day = @day + 1;
        End
    End
End

I think first assignment set @day = 1; wasn't in right place. After increasing @month value you should set also @day to 1;

edited Apr 17, 2016 at 21:12

answered Apr 17, 2016 at 20:39

irakliG.

17610 bronze badges

2 Comments

RalphVB Over a year ago

That was my mistake writing my code here, i hav edited the post to correct it.

RalphVB Over a year ago

HA! Thanks bro, I didn't notice about that (i feel kinda dumb, lol). You are really awesome too :D

Collectives™ on Stack Overflow

Code error in a SQL Server While-If Loop

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related