0537 0537 - 10 months ago 204
SQL Question

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

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.

                        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:

                        WHEN 1 THEN -2 
                        WHEN 2 THEN -3 
                        ELSE -1 
                    END, DATEDIFF(DAY, 0, GETDATE()));

This will work for all language and DATEFIRST settings.