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
Where AsOfdate = Dateadd(dd,-1,Convert(datetime,Convert(varchar(10),'03/28/2012',101),120))
Order By Position_ID
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.