CODEWITHSUNDEEP
sql-serversql-server-2008
SKa
SKa

Reputation: 61

SQL function to populate a simple date table

I need to populate a table with simple (Year, month, days) columns for upto 10 years and I am roughly taking every month to have 30 days.It looks like as below.

I have written the below code to populate the table but I have error on the second 'while' it say 'Expecting '(',or select'. Any clue why?

Simple Date table 

 declare @month varchar(20)
    set @month ='1'

    declare @day varchar(20)
    set @day='1'

    declare @sql nvarchar(1000)
    while(@Year <=10)
     (
      while(@month<=12)
        ( 
          while(@day<= @day+30)
              (
               insert into simple_table values(@Year,@month,@day)
          @day=@day+1 
       )
              @month+1
       )
       @year = @year+1
       )
       )

Upvotes: 1

Views: 503

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

With the help of an ad-hoc tally table and a Cross Join (or two)

Your sample was a little unclear. This assumes the Day column is not 1-30 but 1-3600 for 10 years. I'm assuming you are building some sort of amortization schedule of 30/360

Select Year = 'Year'+cast(Y as varchar(25))
      ,Month = M
      ,Day   = Row_Number() over (Order by Y,M,D)
 From  (Select Top (10) Y=Row_Number() Over (Order By (Select NULL)) From  master..spt_values) Y
 Cross Join (Select Top (12) M=Row_Number() Over (Order By (Select NULL)) From  master..spt_values) M
 Cross Join (Select Top (30) D=Row_Number() Over (Order By (Select NULL)) From  master..spt_values) D

Returns

enter image description here

Upvotes: 1

George Mastros
George Mastros

Reputation: 24498

Seems like it would be a lot easier to use one loop instead of 3. 

Declare @D DateTime;

Set @D = GetDate();

while @D < DateAdd(Year, 10, GetDate())
    Begin

        Insert Into simple_table(Year, Month, Day) Select Year(@D), Month(@D), Day(@D)

        Set @D = DateAdd(Day, 1, @D)

    End

Basically, just use a date as your loop variable, increment one day each time through the loop. Additional benefit, you get exactly the right number of days in each month (including leap years).

Upvotes: 0

Related Questions