クエリでカレンダーを作成する方法です。基準日を軸に1月分のカレンダーを作成します
--基準日 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
コメント