[sql] How to create a Calendar table for 100 years in Sql

This SQL Server User Defined Function resolves the problem efficiently.No recursion, no complex loops. It takes a very short time to generate.

ALTER FUNCTION [GA].[udf_GenerateCalendar]
(
     @StartDate  DATE        -- StartDate
   , @EndDate    DATE        -- EndDate
)
RETURNS @Results TABLE 
       (
           Date       DATE 
       )
AS

/**********************************************************
Purpose:   Generate a sequence of dates based on StartDate and EndDate 
***********************************************************/

BEGIN

    DECLARE @counter INTEGER = 1 

    DECLARE @days table(
        day INTEGER NOT NULL 
    )

    DECLARE @months table(
        month INTEGER NOT NULL 
    )

    DECLARE @years table(
        year INTEGER NOT NULL 
    )

    DECLARE @calendar table(
        Date DATE NOT NULL 
    )


    -- Populate generic days 
    SET @counter = 1 
    WHILE @counter <= 31 
    BEGIN 
        INSERT INTO @days 
        SELECT @counter dia 

        SELECT @counter = @counter + 1 
    END 

    -- Populate generic months 
    SET @counter = 1 
    WHILE @counter <= 12 
    BEGIN 
        INSERT INTO @months 
        SELECT @counter month 

        SELECT @counter = @counter + 1 
    END 

    -- Populate generic years 
    SET @counter = YEAR(@StartDate) 
    WHILE @counter <= YEAR(@EndDate) 
    BEGIN 
        INSERT INTO @years 
        SELECT @counter year 

        SELECT @counter = @counter + 1 
    END 

    INSERT @calendar (Date) 
    SELECT Date 
    FROM ( 
        SELECT 
            CONVERT(Date, [Date], 102) AS Date 
        FROM ( 
            SELECT 
                CAST(
                    y.year * 10000 
                    + m.month * 100 
                    + d.day 
                    AS VARCHAR(8)) AS Date 
            FROM @days d, @months m, @years y 
            WHERE 
                ISDATE(CAST(
                    y.year * 10000 
                    + m.month * 100 
                    + d.day 
                    AS VARCHAR(8)) 
                    ) = 1 
        ) A 
    ) A 

    INSERT @Results (Date) 
    SELECT Date 
    FROM @calendar 
    WHERE Date BETWEEN @StartDate AND @EndDate

   RETURN 
/*
DECLARE @StartDate DATE = '2015-08-01'
DECLARE @EndDate   DATE = '2015-08-31'
select * from [GA].[udf_GenerateCalendar](@StartDate, @EndDate)
*/
END