plenty of calendar scripts available if you search...heres a snippet....see last column

E&OE

regards

--==== Create a Tally table and a Calendar table

SELECT TOP 60001 IDENTITY(INT, 1, 1) AS N --=== will have start as 1...alter to IDENTITY(INT, 0, 1) for zero as start

INTO dbo.Tally

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

SET DATEFORMAT DMY

DECLARE @Date_Start AS DATETIME

DECLARE @Date_End AS DATETIME

SET @Date_Start = '01/01/2010'

SET @Date_End = '31/12/2020'

CREATE TABLE dbo.Calendar

(

calendar_date_ID INT IDENTITY(1, 1) NOT NULL,

calendar_week_ID INT,

calendar_month_ID INT,

calendar_date DATETIME PRIMARY KEY CLUSTERED,

calendar_year SMALLINT,

calendar_month TINYINT,

calendar_day TINYINT,

calendar_quarter TINYINT,

first_day_in_month DATETIME,

last_day_in_month DATETIME,

day_of_week TINYINT,

week_of_year TINYINT,

days_in_month TINYINT,

day_of_year SMALLINT,

is_weekday INT,

day_name VARCHAR (10),

month_name VARCHAR (10),

iso_date CHAR (8),

fiscal_year SMALLINT,

fiscal_month TINYINT,

DIGS_MTH INT

);

INSERT INTO dbo.Calendar

(calendar_date)

SELECT t.N - 1 + @Date_Start

FROM dbo.Tally t

WHERE t.N - 1 + @Date_Start <= @Date_End

UPDATE dbo.Calendar

SET calendar_week_ID = calendar_date_id / 7 + 1,

calendar_year = Datepart (YEAR, calendar_date),

fiscal_year = CASE

WHEN Datepart(M, calendar_date) >= 10 THEN Datepart (YEAR, calendar_date) + 1

ELSE Datepart (YEAR, calendar_date)

END,

calendar_month = Datepart (MONTH, calendar_date),

fiscal_month = CASE

WHEN Datepart(M, calendar_date) >= 10 THEN Datepart(M, calendar_date) - 9

ELSE Datepart(M, calendar_date) + 3

END,

calendar_day = Datepart (DAY, calendar_date),

calendar_quarter = Datepart (QUARTER, calendar_date),

first_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date), 0),

last_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1,

day_of_week = Datepart (WEEKDAY, calendar_date),

week_of_year = Datepart (WEEK, calendar_date),

day_of_year = Datepart (DAYOFYEAR, calendar_date),

is_weekday = Isnull (( CASE

WHEN ( ( @@DATEFIRST - 1 ) + ( Datepart (WEEKDAY, calendar_date) - 1 ) )%7 NOT IN ( 5, 6 )

THEN 1

END ), 0),

day_name = Datename (WEEKDAY, calendar_date),

month_name = Datename (MONTH, calendar_date),

iso_date = CONVERT(CHAR(8), calendar_date, 112),

days_in_month = Datepart(dd, ( Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1 )),

DIGS_MTH = CONVERT(CHAR(6), calendar_date, 112)