Here's my version of a Calendar table;

I put a bit of time into it putting in various US holidays, which might be of some help as well.

my date range is from SQL beginning of time(1900-01-01) to getdate() + 100 years .

TallyCalendar_Complete_With_DST.txt

Mark's example has some nice calculated columns in it ; mine is not quite so thourough as his is:

the final table has a definition that looks like this:

CREATE TABLE [dbo].[TallyCalendar] (

[TheDate] datetime NOT NULL,

[DayOfWeek] varchar(50) NULL,

[IsHoliday] bit NULL DEFAULT ((0)),

[IsWorkHoliday] bit NULL DEFAULT ((0)),

[IsWeekDay] bit NULL DEFAULT ((0)),

[IsWeekEnd] bit NULL DEFAULT ((0)),

[IsDaylightSavings] bit NULL DEFAULT ((0)),

[HolidayName] varchar(100) NULL,

[LunarPhase] varchar(50) NULL,

[IsoWeek] int NULL,

[IsWorkDay] AS (case when [IsWorkHoliday]=(1) OR ([DayOfWeek]='Sunday' OR [DayOfWeek]='Saturday') then (0) else (1) end) PERSISTED,

[JulianDay] AS (datediff(day,dateadd(year,datediff(year,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,

[YearNumber] AS (datepart(year,[Thedate])) PERSISTED,

[MonthNumber] AS (datediff(month,dateadd(year,datediff(year,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,

[DayNumber] AS (datediff(day,dateadd(month,datediff(month,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,

CONSTRAINT [PK__TallyCal__5CB7C64E1A14E395] PRIMARY KEY CLUSTERED (TheDate))

GO