Function which returns holidays of the year

Here I am attaching code of functiuon which returns the holidays of the given year


CREATE FUNCTION Fn_Holiday_Of_Year(@in_CurrentYear CHAR(4) )
RETURNS @Y_Holidays TABLE
(
Dt SMALLDATETIME
)

AS
/************************************************** ************************************

Function NAME: Fn_Holiday_Of_Year

CALLED FROM: ----

PURPOSE: Returns the bank holidays of the given year( 11 Days )

TABLES USED AND ACTIONS DONE

INPUT : YEAR 'YYYYY'
OUTPUT : List of the holidays

MODIFICATION HISTORY
Person Date Comments
Vinod Patel Dec 19, 2008 Created the function

************************************************** **********************************/
BEGIN

DECLARE
@Holidays TABLE
(
Dt SMALLDATETIME
)
DECLARE
@NewYear AS DATETIME,
@Canada_Day AS DATETIME,
@Remembrance_Day AS DATETIME,
@Christmas_Day AS DATETIME,
@Boxing_Day AS DATETIME,
@Family_Day AS DATETIME,
@Civic_Day AS DATETIME,
@Labour_Day AS DATETIME,
@Thanksgiving_Day AS DATETIME,
@Victoria_Day AS DATETIME,
@Good_Friday AS DATETIME,

--Variable to calculate Easter
@Year char(4),
@c INT
, @n INT
, @k INT
, @i INT
, @j INT
, @l INT
, @m INT
, @d INT
, @Easter DATETIME
--================================================== ====================================
--STATIC HOLYDAY : New Year,Canada Day,Remembrance Day,Christmas Day and Boxing Day
--================================================== ====================================

--New Year
SET @NewYear = @in_CurrentYear+'-01-01'

--IF New Year’s Day falls on a Saturday or Sunday the holiday wil be on the working day immediately before or after the New Year’s Day.
IF DATENAME(dw,@NewYear)='Saturday'
SET @NewYear = @NewYear- 1
IF DATENAME(dw,@NewYear)='Sunday'
SET @NewYear = @NewYear + 1

INSERT INTO @Holidays
VALUES( @NewYear)


--Canada Day
SET @Canada_Day = @in_CurrentYear+'-07-01'

--IF Canada Day falls on a Saturday or Sunday the holiday wil be on the working day immediately before or after the New Year’s Day.
IF DATENAME(dw,@Canada_Day)='Saturday'
SET @Canada_Day = @Canada_Day- 1
IF DATENAME(dw,@Canada_Day)='Sunday'
SET @Canada_Day = @Canada_Day + 1

INSERT INTO @Holidays
VALUES( @Canada_Day)

--Remembrance Day
SET @Remembrance_Day = @in_CurrentYear+'-11-11'

--IF Remembrance Day falls on a Saturday or Sunday the holiday wil be on the working day immediately before or after the New Year’s Day.
IF DATENAME(dw,@Remembrance_Day)='Saturday'
SET @Remembrance_Day = @Remembrance_Day- 1
IF DATENAME(dw,@Remembrance_Day)='Sunday'
SET @Remembrance_Day = @Remembrance_Day + 1

INSERT INTO @Holidays
VALUES( @Remembrance_Day)

--Christmas Day

SET @Christmas_Day = @in_CurrentYear+'-12-25'

--IF Christmas Day falls on a Saturday or Sunday the holiday wil be on the working day immediately before or after the New Year’s Day.
IF DATENAME(dw,@Christmas_Day)='Saturday'
SET @Christmas_Day = @Christmas_Day- 1
IF DATENAME(dw,@Christmas_Day)='Sunday'
SET @Christmas_Day = @Christmas_Day + 1

INSERT INTO @Holidays
VALUES( @Christmas_Day)


--Boxing Day
SET @Boxing_Day = @in_CurrentYear+'-12-26'

--The holiday will be the working day immediately after the Christmas Day holiday.
IF DATENAME(dw,@Christmas_Day)='Friday'
SET @Boxing_Day = @Christmas_Day+ 3
IF DATENAME(dw,@Christmas_Day)='Monday'
SET @Boxing_Day = @Christmas_Day + 1

INSERT INTO @Holidays
VALUES( @Boxing_Day)

--================================================== ====================================
--DYNAMIC HOLYDAY : These holidays occur on a specific day(MONDAY) of week in a specific week and month.
--================================================== ====================================


--Family Day is a statutory holiday that takes place on the third Monday of February

SET @Family_Day = @in_CurrentYear+'-02-01'

IF DATENAME(dw,@Family_Day)='Sunday'
SET @Family_Day = @Family_Day + 15
ELSE IF DATENAME(dw,@Family_Day)='Monday'
SET @Family_Day = @Family_Day + 14
ELSE IF DATENAME(dw,@Family_Day)='Tuesday'
SET @Family_Day = @Family_Day + 20
ELSE IF DATENAME(dw,@Family_Day)='Wednesday'
SET @Family_Day = @Family_Day + 19
ELSE IF DATENAME(dw,@Family_Day)='Thursday'
SET @Family_Day = @Family_Day + 18
ELSE IF DATENAME(dw,@Family_Day)='Friday'
SET @Family_Day = @Family_Day + 17
ELSE IF DATENAME(dw,@Family_Day)='Saturday'
SET @Family_Day = @Family_Day + 16

INSERT INTO @Holidays
VALUES( @Family_Day)



--Civic Holiday is on the first Monday of August

SET @Civic_Day = @in_CurrentYear+'-08-01'

IF DATENAME(dw,@Civic_Day)='Sunday'
SET @Civic_Day = @Civic_Day + 1
ELSE IF DATENAME(dw,@Civic_Day)='Monday'
SET @Civic_Day = @Civic_Day + 0
ELSE IF DATENAME(dw,@Civic_Day)='Tuesday'
SET @Civic_Day = @Civic_Day + 6
ELSE IF DATENAME(dw,@Civic_Day)='Wednesday'
SET @Civic_Day = @Civic_Day + 5
ELSE IF DATENAME(dw,@Civic_Day)='Thursday'
SET @Civic_Day = @Civic_Day + 4
ELSE IF DATENAME(dw,@Civic_Day)='Friday'
SET @Civic_Day = @Civic_Day + 3
ELSE IF DATENAME(dw,@Civic_Day)='Saturday'
SET @Civic_Day = @Civic_Day + 2

INSERT INTO @Holidays
VALUES( @Civic_Day)


-- Labour Day (First Monday in September)

SET @Labour_Day = @in_CurrentYear+'-09-01'

IF DATENAME(dw,@Labour_Day)='Sunday'
SET @Labour_Day = @Labour_Day + 1
ELSE IF DATENAME(dw,@Labour_Day)='Monday'
SET @Labour_Day = @Labour_Day + 0
ELSE IF DATENAME(dw,@Labour_Day)='Tuesday'
SET @Labour_Day = @Labour_Day + 6
ELSE IF DATENAME(dw,@Labour_Day)='Wednesday'
SET @Labour_Day = @Labour_Day + 5
ELSE IF DATENAME(dw,@Labour_Day)='Thursday'
SET @Labour_Day = @Labour_Day + 4
ELSE IF DATENAME(dw,@Labour_Day)='Friday'
SET @Labour_Day = @Labour_Day + 3
ELSE IF DATENAME(dw,@Labour_Day)='Saturday'
SET @Labour_Day = @Labour_Day + 2

INSERT INTO @Holidays
VALUES( @Labour_Day)



-- Thanks giving Day (Second Monday in October)

SET @Thanksgiving_Day = @in_CurrentYear+'-10-01'

IF DATENAME(dw,@Thanksgiving_Day)='Sunday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 8
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Monday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 7
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Tuesday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 13
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Wednesday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 12
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Thursday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 11
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Friday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 10
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Saturday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 9

INSERT INTO @Holidays
VALUES( @Thanksgiving_Day)


--Victoria Day (Monday on or before May 24)

SET @Victoria_Day = @in_CurrentYear+'-05-24'

IF DATENAME(dw,@Victoria_Day)='Sunday'
SET @Victoria_Day = @Victoria_Day - 6
ELSE IF DATENAME(dw,@Victoria_Day)='Monday'
SET @Victoria_Day = @Victoria_Day - 0
ELSE IF DATENAME(dw,@Victoria_Day)='Tuesday'
SET @Victoria_Day = @Victoria_Day - 1
ELSE IF DATENAME(dw,@Victoria_Day)='Wednesday'
SET @Victoria_Day = @Victoria_Day - 2
ELSE IF DATENAME(dw,@Victoria_Day)='Thursday'
SET @Victoria_Day = @Victoria_Day - 3
ELSE IF DATENAME(dw,@Victoria_Day)='Friday'
SET @Victoria_Day = @Victoria_Day - 4
ELSE IF DATENAME(dw,@Victoria_Day)='Saturday'
SET @Victoria_Day = @Victoria_Day - 5

INSERT INTO @Holidays
VALUES( @Victoria_Day)

--Good Friday (Friday before Easter Sunday)

-- Ester Day Calculation

SET @Year = @in_CurrentYear
SET @c = (@Year / 100)
SET @n = @Year - 19 * (@Year / 19)
SET @k = (@c - 17) / 25
SET @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15
SET @i = @i - 30 * ( @i / 30 )
SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11))
SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
SET @j = @j - 7 * (@j / 7)
SET @l = @i - @j
SET @m = 3 + (@l + 40) / 44
SET @d = @l + 28 - 31 * ( @m / 4 )

SET @Easter = (SELECT RIGHT('0' + CONVERT(VARCHAR(2),@m),2) + '/' + RIGHT('0' + CONVERT(VARCHAR(2),@d),2) + '/' + CONVERT(CHAR(4),@Year))

SET @Good_Friday = @Easter -2

INSERT INTO @Holidays
VALUES( @Good_Friday)

INSERT INTO @Y_Holidays
SELECT DT FROM @Holidays ORDER BY dt

RETURN

END