SQL Server　クエリでカレンダーを作成する方法

クエリでカレンダーを作成する方法です。基準日を軸に１月分のカレンダーを作成します

--基準日
DECLARE @toDay   AS DATE = '2019-11-07';
--月初
DECLARE @mStart  AS DATE = dateadd(dd,-datepart(dd,@toDay)+1, @toDay);
--月末
DECLARE @mEnd    AS DATE = dateadd(dd ,-datepart(dd,dateadd(mm,1,@toDay)),dateadd(mm,1,@toDay));

-- テーブル変数を定義
DECLARE @tmpCalendar TABLE
(
 [Date] DATE
,[WeekDay] INT
);

WITH DateTable ([Date],[WeekDay])
AS
(
    SELECT @mStart,DATEPART(WEEKDAY,@mStart)
    UNION ALL
    SELECT DATEADD(dd, 1, [Date]),DATEPART(WEEKDAY,DATEADD(dd, 1, [Date]))
    FROM DateTable
    WHERE [Date] < @mEnd
)
INSERT INTO @tmpCalendar
SELECT [Date],[WeekDay]
FROM DateTable
OPTION (MAXRECURSION 0);

SELECT * FROM @tmpCalendar