How can i create a Date table in SQL that will automatically fill itself

Question

I wanna create a table in SQL with the following 4 colums: id, year, month and day. But i wanna fill this colums automatically from the year of 2012 to 2020. What is the best way to do this? i googled but i couln't really find a solution. For example i want the table to show the following information.

ID  year  Month Day
1   2013   1     2
2   2013   1     3
3   2013   1     4
4   2013   1     5

Please add the tag of the database you use, for example: Oracle, MySQL, SQLServer... — VBoka, Sep 25 '20 at 14:50
In SQL, you first create the table, then INSERT the data. You can use a recursive cte to insert all these dates. — jarlh, Sep 25 '20 at 14:51
This looks like a good article... https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ — VBoka, Sep 25 '20 at 14:53
More often than not, these are referred to as `calendar table`. There are plenty of examples out there. — Damien_The_Unbeliever, Sep 25 '20 at 14:54
Aslo here: https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql and here https://gist.github.com/pimbrouwers/c8613ad33edbe22078e1c55c862ee7f8 — VBoka, Sep 25 '20 at 14:55
I use this [tvf](https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function) it's flexible and quick — SteveC, Sep 25 '20 at 15:06

score 1 · Answer 1 · answered Sep 25 '20 at 20:44

There is no need for loops, cursors or recursive CTEs... It's as easy as the following.

    WITH 
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100
        cte_Calendar (dt) AS (
            SELECT TOP (DATEDIFF(DAY, '2012-01-01', '2020-12-31') + 1)
                CONVERT(DATE, DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '2012-01-01'))
            FROM
                cte_n2 a CROSS JOIN cte_n2 b                                                    -- 10,000
            )
    SELECT
        ID = DATEDIFF(DAY, '2012-01-01', c.dt) + 1,
        [Year] = YEAR(c.dt),
        [Month] = MONTH(c.dt),
        [Day] = DAY(c.dt)
    FROM
        cte_Calendar c;
GO

Or you can use it to populate a permanent table...

    IF OBJECT_ID('tempdb.dbo.YMD', 'U') IS NOT NULL 
    BEGIN DROP TABLE tempdb.dbo.YMD; END;
    GO
    
    CREATE TABLE tempdb.dbo.YMD (
            ID int NOT NULL IDENTITY(1,1)
                CONSTRAINT pk_YMD PRIMARY KEY CLUSTERED (ID),
            [Year] int NOT NULL,
            [Month] tinyint NOT NULL,
            [Day] tinyint NOT NULL 
        );
    GO
    
    --------------------------------------------------------------------------------------------------------
    WITH 
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100
        cte_Calendar (dt) AS (
            SELECT TOP (DATEDIFF(DAY, '2012-01-01', '2020-12-31') + 1)
                CONVERT(DATE, DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '2012-01-01'))
            FROM
                cte_n2 a CROSS JOIN cte_n2 b                                                    -- 10,000
            )
    INSERT tempdb.dbo.YMD (Year, Month, Day)
    SELECT 
        [Year] = YEAR(c.dt),
        [Month] = MONTH(c.dt),
        [Day] = DAY(c.dt)
    FROM
        cte_Calendar c;
GO
    
    ------------------------------------------------------------------------------------------------------
    SELECT
        YMD.ID,
        YMD.Year,
        YMD.Month,
        YMD.Day
    FROM
        tempdb.dbo.YMD;
GO

score 0 · Answer 2 · answered Sep 25 '20 at 15:52

Using the VBoka suggestion, try this:

create table calendar(
    id int identity(1,1) not null primary key,
    ano int not null,
    mes int not null,
    dia int not null
)

declare @startdate date, @enddate date, @num_days int
set @startdate = '2012-01-01'
set @num_days = 3287 --number of days bewtween jan 2012 and dez 2020 https://www.timeanddate.com/date/durationresult.html
set @enddate = dateadd(d, @num_days, @startdate)

while @startdate <= @enddate
    begin
        insert into calendar (ano,mes,dia)
            select year(@startdate), month(@startdate), day(@startdate)

        set @startdate = dateadd(dd, 1, @startdate)
    end

score 0 · Answer 3 · answered Sep 25 '20 at 19:59

Using CTE and no datetime functions:

if object_id('Calendar_tb','U') is null
    create table Calendar_tb (
        id int identity(1,1) not null,
        [year] smallint, [month] tinyint, [day] tinyint
    )

declare @tb table (yy smallint, mm tinyint, dd tinyint);
with
CTE_y as (
    select 2012 as nyear
    union all
    select nyear + 1 from CTE_y where nyear < 2020
),
CTE_m as (
    select 1 as nmonth
    union all
    select nmonth + 1 from CTE_m where nmonth < 12
),
CTE_d as (
    select 1 as nday
    union all
    select nday + 1 from cte_d where nday < 31
)
insert @tb
select nyear, nmonth, nday from CTE_d, CTE_m, CTE_y

delete from @tb
where   (dd>30 and mm in (4, 6, 9, 11))
    or  (dd>29 and mm = 2)
    or  (dd>28 and mm = 2 and not
            -- leap year
            (yy%400=0 or (yy%4=0 and yy%100!=0)))

truncate table Calendar_tb

insert Calendar_tb
select * from @tb order by 1, 2, 3

select * from Calendar_tb

Considering how bad recursive CTEs perform, especially for reads, you should consider practicing to avoid them... even for small stuff. — Jeff Moden, Sep 25 '20 at 22:55

How can i create a Date table in SQL that will automatically fill itself

3 Answers3