Get last Friday's Date unless today is Friday using T-SQL

user_640205 0
Viewed 51562

I'm trying to get the correct SQL code to obtain last Friday's date. A few days ago, I thought I had my code correct. But just noticed that it's getting last week's Friday date, not the last Friday. The day I'm writing this question is Saturday, 8/11/2012 @ 12:23am. In SQL Server, this code is returning Friday, 8/3/2012. However, I want this to return Friday, 8/10/2012 instead. How can I fix this code? Since we're getting to specifics here, if the current day is Friday, then I want to return today's date. So if it were yesterday (8/10/2012) and I ran this code yesterday, then I would want this code to return 8/10/2012, not 8/3/2012.

SELECT DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
dayofweek tsql sql-server sql
11 Answers

None of that? Try this:

DECLARE @D DATE = GETDATE()
SELECT DATEADD(D,-(DATEPART(W,@D)+1)%7,@D)
user_52146630 
select convert(varchar(10),dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate()),101)
user_29695960
Related