Creating a table of sequences (integers and dates)

Question

Sometimes I need to create tables of regularly occuring sequences of dates or integers. I manually create them and that works but it's more difficult to maintain because there's a lot of code duplication. What is the more maintainable way to say a sequence of integers or dates that occur at regular intervals?

Here's my current approach:

DECLARE @IndexDate TABLE (
[Id] INT,
[Date] DATE
)
INSERT INTO @IndexDate (
Id, Date
)
VALUES 
(1, CONCAT(YEAR(GETDATE()), '-01-01')),
(2, CONCAT(YEAR(GETDATE()), '-02-01')),
(3, CONCAT(YEAR(GETDATE()), '-03-01')),
(4, CONCAT(YEAR(GETDATE()), '-04-01')),
(5, CONCAT(YEAR(GETDATE()), '-05-01')),
(6, CONCAT(YEAR(GETDATE()), '-06-01')),
(7, CONCAT(YEAR(GETDATE()), '-07-01')),
(8, CONCAT(YEAR(GETDATE()), '-08-01')),
(9, CONCAT(YEAR(GETDATE()), '-09-01')),
(10, CONCAT(YEAR(GETDATE()), '-10-01')),
(11, CONCAT(YEAR(GETDATE()), '-11-01')),
(12, CONCAT(YEAR(GETDATE()), '-12-01'))

SELECT * FROM @IndexDate

You should look into recursive CTEs e.g. https://stackoverflow.com/questions/37835334/how-to-create-recursive-query-to-get-all-dates-between-two-dates — Nick, May 17 '19 at 14:03

score 2 · Answer 1 · answered May 17 '19 at 14:03

You could use recursive cte:

WITH cte(n) AS (
   SELECT 1 
   UNION ALL
   SELECT n+ 1 
   FROM cte  
   WHERE n < 12
)
SELECT * 
FROM cte
OPTION (maxrecursion 0);

WITH cte(d) AS (
   SELECT CAST('20190101' AS DATE)
   UNION ALL
   SELECT DATEADD(m, 1, d)
   FROM cte  
   WHERE d < '20200101'
)
SELECT * 
FROM cte
OPTION (maxrecursion 0);

db<>fiddle demo

score 1 · Accepted Answer · answered May 17 '19 at 14:10

To match your logic with a recursive CTE, you can do:

with indextable as (
      select 1 as id, datefromparts(year(getdate()), 1, 1) as date
      union all
      select 1 + id, dateadd(month, 1, date)
      from indextable
      where id < 12
     )
select *
from indextable;

For one year, you don't have to worry about option (maxrecursion).

I'm not a big fan of using date as a column name, because it is a keyword, but SQL Server allows it.

Creating a table of sequences (integers and dates)

2 Answers2