The code for figuring out the week of the month is built in SQL functionality... I don't know that there is a way, non-manual, to calculate week of month if the week starts on Monday instead of Sunday...

I'm sure you can do it by putting in a loop and checking day one... and counting the days from there... etc... but that would take a lot of code and a lot of time to loop through every day for 150 years...

Yeah... it was a bit of work getting it to this point and I wish someone had done it like this before me to save me all that time... but I figured I'd save everyone else a bit of time... 😉

Your Welcome... Your Welcome... Your Welcome...

PS>... For the holiday's comment... yeah... Those are mostly manual as you can see in the code... pick and choose your own... and if anyone has a way of programatically calculating when the Jewish calender holidays are...etc... I'd love to see it and add it to this code for everyone.

As for easter... I found the code posted by

AUTHOR: Robert Davis

SOURCE: http://www.databasejournal.com/scripts/article.php/3469911/Calculate-Easter-for-Any-Year.htm

Add this code to the script I posted after you add the function from the link above.

--Easter ------------------------------------------------------------------------------------------------------

DECLARE @Years TABLE([ID] INT IDENTITY(1,1), [YEAR][INT])

INSERT INTO @YEARS([YEAR])

SELECT DISTINCT YEAR

FROM [dim_DATE]

DECLARE @POS BIGINT, @CNTR BIGINT, @YEAR VARCHAR(4)

SELECT @POS = 1, @CNTR = MAX([ID])

FROM @YEARS

WHILE @POS <= @CNTR

BEGIN

SELECT @YEAR = [YEAR]

FROM @YEARS

WHERE ID = @POS

UPDATE dim_DATE

SET HolidayText = ISNULL(HolidayText,'') + 'Easter'

WHERE DATE = CAST(dbo.Year2Easter(@YEAR) AS DATETIME)

SELECT @POS = @POS + 1

END

GO

------------------------------------------------------------------------