While browsing through various blogs and articles, I came across a ColdFusion blog post by Ben Nadel titled “Getting the Previous Day In ColdFusion, Excluding Saturday And Sunday.” It got me thinking about a similar function that I often use in my SQL Server database. This function allows me to exclude weekends (Saturday and Sunday) and retrieve the previous or next working day.

Let’s take a look at the script to create this function:

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 the previous working day
    IF @strPrevNext = 'Previous'
    BEGIN
        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)
    END

    -- To find the next working day
    ELSE IF @strPrevNext = 'Next'
    BEGIN
        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)
    END

    -- Default case returns the date passed to the function
    ELSE
        SET @rtResult = @dtDate

    RETURN @rtResult
END

Now, let’s see some examples of how we can use this function:

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')

By executing the above examples, we can obtain the desired results.

I personally find this function to be one of the most interesting and useful functions related to working with dates and times in SQL Server. It allows us to easily exclude weekends and retrieve the previous or next working day. I highly recommend bookmarking this function for future reference.

Thank you for reading! I hope you found this blog post informative and helpful. If you have any thoughts or questions, please feel free to share them.