How to get Previous business day in a week with that of current Business Day using sql server

Question

i have an ssis Package which runs on business days (mon-Fri). if i receive file on tuesday , background(DB), it takes previous business day date and does some transactions. If i run the job on friday, it has to fetch mondays date and process the transactions.

i have used the below query to get previous business date

Select Convert(varchar(50), Position_ID) as Position_ID,
       TransAmount_Base,
       Insert_Date as InsertDate
  from tblsample
 Where AsOfdate = Dateadd(dd, -1, Convert(datetime, Convert(varchar(10), '03/28/2012', 101), 120))
Order By Position_ID

if i execute this query i'll get the results of yesterdays Transactios. if i ran the same query on monday, it has to fetch the Fridays transactions instead of Sundays.

Answer 1

SELECT  DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
                        WHEN 'Sunday' THEN -2 
                        WHEN 'Monday' THEN -3 
                        ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))

I prefer to use DATENAME for things like this over DATEPART as it removes the need for Setting DATEFIRST And ensures that variations on time/date settings on local machines do not affect the results. Finally DATEDIFF(DAY, 0, GETDATE()) will remove the time part of GETDATE() removing the need to convert to varchar (much slower).

---

EDIT (almost 2 years on)

This answer was very early in my SO career and it annoys me everytime it gets upvoted because I no longer agree with the sentiment of using DATENAME.

A much more rubust solution would be:

SELECT  DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 
                        WHEN 1 THEN -2 
                        WHEN 2 THEN -3 
                        ELSE -1 
                    END, DATEDIFF(DAY, 0, GETDATE()));

This will work for all language and DATEFIRST settings.

Answer 2

This function returns last working day and takes into account holidays and weekends. You will need to create a simple holiday table.

-- =============================================
-- Author:      Dale Kilian
-- Create date: 2019-04-29
-- Description: recursive function returns last work day for weekends and 
-- holidays
-- =============================================
ALTER FUNCTION dbo.fnGetWorkWeekday
(
    @theDate DATE
)
RETURNS DATE
AS
BEGIN

DECLARE @importDate DATE = @theDate
DECLARE @returnDate DATE
--Holidays
IF EXISTS(SELECT 1 FROM dbo.Holidays WHERE isDeleted = 0 AND @theDate = Holiday_Date)
BEGIN
SET @importDate = DATEADD(DAY,-1,@theDate);
SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
--Satruday
IF(DATEPART(WEEKDAY,@theDate) = 7)
BEGIN
    SET @importDate = DATEADD(DAY,-1,@theDate);
    SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
--Sunday
IF(DATEPART(WEEKDAY,@theDate) = 1)
BEGIN
    SET @importDate = DATEADD(DAY,-2,@theDate);
    SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END


RETURN @importDate;
END
GO