IsWorkday UDF without a table
While the other methods for calculating holidays ect use a table to store the holidays, this inline UDF goes the opposite route, by returning 1 if the given date is a weekday that also does not fall on a number of holidays. Examples shown allow for easy extension to other holidays as deemed necessary.
/*****************************************************************************
* funcIsWorkday - Returns 1 if the specified date is a M-F workday that does
* not fall on any of the following holidays:
* New Years Day, Memorial Day, Independence Day, Labor Day,
* Thanksgiving Day and day after, Christmas even, Christmas
* day after Christmas, New Years Eve
******************************************************************************/
CREATE FUNCTION [funcIsWorkday]
(
@dtDate datetime
)
RETURNS int AS
BEGIN
DECLARE @inDW int
DECLARE @inMonth int
DECLARE @inDay int
DECLARE @inYear int
DECLARE @dtTemp datetime
SET @inDW = DATEPART(dw,@dtDate)
-- Saturday or Sunday
IF (@inDW = 1 OR @inDW = 7)
RETURN 0
-- New years day
IF (@inMonth = 1 AND @inDay = 1)
RETURN 0
-- Memorial Day
IF (@inMonth = 5)
BEGIN
-- Last Monday in the month of May
SET @dtTemp = CAST(('6/1/' + CAST(@inYear AS varchar)) AS datetime)
SET @dtTemp = CASE(DATEPART(dw,@dtTemp))
WHEN 1 THEN DATEADD(dd,-6,@dtTemp) -- Sunday after
WHEN 2 THEN DATEADD(dd,-7,@dtTemp) -- Monday week after
ELSE DATEADD(dd,2-DATEPART(dw,@dtTemp),@dtTemp)
END
IF (@inDay = DAY(@dtTemp))
RETURN 0
END
-- Independence Day
IF (@inMonth = 7 AND @inDay = 4)
RETURN 0
-- Labor Day
-- First Monday in September
IF (@inMonth = 9)
BEGIN
SET @dtTemp = CAST(('9/1/' + CAST(@inYear AS varchar)) AS datetime)
SET @dtTemp = CASE(DATEPART(dw,@dtTemp))
WHEN 1 THEN DATEADD(dd,1,@dtTemp) -- Before Mondy
WHEN 2 THEN @dtTemp -- First of the month
ELSE DATEADD(dd,(7-DATEPART(dw,@dtTemp)+2),@dtTemp) -- After Monday
END
IF (@inDay = DAY(@dtTemp))
RETURN 0
END
-- Thanksgiving Day or day after
-- Fourth Thursday/Friday in November
IF (@inMonth = 11)
BEGIN
SET @dtTemp = CAST(('11/1/' + CAST(@inYear AS varchar)) AS datetime)
SET @dtTemp = CASE
WHEN DATEPART(dw,@dtTemp) < 5 THEN
DATEADD(dd,5-DATEPART(dw,@dtTemp)+21,@dtTemp) -- Before thursday
WHEN DATEPART(dw,@dtTemp) = 5 THEN
DATEADD(dd,21,@dtTemp) -- Thursday
ELSE DATEADD(dd,7-DATEPART(dw,@dtTemp)+5+21,@dtTemp) -- After thusday
END
IF (@inDay = DAY(@dtTemp))
RETURN 0 -- Thanksgiving
IF (@inDay - 1 = DAY(@dtTemp))
RETURN 0 -- Day after
END
-- Christmas eve, xmas, day after, new years eve
IF (@inMonth = 12 AND
(@inDay = 24 OR @inDay = 25 OR @inDay = 26 OR @inDay = 31))
RETURN 0
-- Must be a workday
RETURN 1
END
