SQL Previous Business Day Function

This is a simple function for MS SQL that can make reporting so much easier by finding the previous business day.  

Setup the function:  

CREATE FUNCTION [dbo].[fn_prevbusday](@date DATETIME)

RETURNSDATETIMEAS

BEGIN

DECLARE @prevbusday DATETIME, @is_not_businessday BIT

SET @prevbusday = dateadd(day, –1, @date)

SET @is_not_businessday = 1

WHILE @is_not_businessday > 0

BEGIN

 IF EXISTS(SELECT holiday_date FROM tbl_Lib_Holidays WHERE holiday_date = @prevbusday)

  SET @prevbusday = dateadd(day, –1, @prevbusday)

 ELSE

  BEGIN

   –@@DATEFIRST = 7 -> Sunday

   IF @@DATEFIRST = 7 AND DATEPART(WEEKDAY, @prevbusday) BETWEEN 2 AND 6

    SET @is_not_businessday = 0

   ELSE

    SET @prevbusday = dateadd(day, –1, @prevbusday)

   END

 END

RETURN @prevbusday

END

Create your Holidays table:

CREATE TABLE [dbo].[tbl_Lib_Holidays](

[ID] [int] IDENTITY(1,1) NOT NULL,

[holiday] [nchar](40) NULL,

[holiday_date] [datetime] NULL

) ON [PRIMARY]

Now you can use the new function in your code:

select dbo.fn_prevbusday(getdate())