SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday

July 23, 2007

While reading ColdFusion blog of Ben Nadel Getting the Previous Day In ColdFusion, Excluding Saturday And Sunday, I realize that I use similar function in my SQL Server Database. This function excludes the Weekends (Saturday and Sunday), and it gets previous as well as next work day.

Script to create a function to get previous and next working day.

CREATE FUNCTION dbo.udf_GetPrevNextWorkDay (@dtDate DATETIME, @strPrevNext VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
DECLARE @intDay INT
DECLARE @rtResult DATETIME
SET @intDay = DATEPART(weekday,@dtDate)
--To find Previous working day
IF @strPrevNext = 'Previous'
IF @intDay = 1
SET @rtResult = DATEADD(d,-2,@dtDate)
ELSE
IF @intDay = 2
SET @rtResult = DATEADD(d,-3,@dtDate)
ELSE
SET @rtResult = DATEADD(d,-1,@dtDate)
--To find Next working day
ELSE
IF @strPrevNext = 'Next'
IF @intDay = 6
SET @rtResult = DATEADD(d,3,@dtDate)
ELSE
IF @intDay = 7
SET @rtResult = DATEADD(d,2,@dtDate)
ELSE
SET @rtResult = DATEADD(d,1,@dtDate)
--Default case returns date passed to function
ELSE
SET @rtResult = @dtDate
RETURN @rtResult
END
GO

Following examples will execute the above function and provide the desired results.

SELECT dbo.udf_GetPrevNextWorkDay('1/1/2007','Previous')
SELECT dbo.udf_GetPrevNextWorkDay('1/1/2007','Next')
SELECT dbo.udf_GetPrevNextWorkDay('12/31/2007','Previous')
SELECT dbo.udf_GetPrevNextWorkDay('12/31/2007','Next')

Let me know what you think of this blog post. I personally found this one of the most interesting functions related to daytime and worth bookmarking.

Reference : Pinal Dave (https://blog.sqlauthority.com)