ODBC – Exclude weekends in SQL

I currently have this SELECT statement which allows me to get yesterday’s date:

SELECT DATEADD(DD, -1, CAST(GETDATE() as DATE))

However, when the day is Monday, I need the result to return a value date for Friday and not Sunday. When the day is Tuesday to Friday, it would be the SELECT Statement mentioned above.

What would be the right code line to add in order to have a value for Friday when the day is Monday, and -1 day when it’s Tuesday to Friday?

Thank you for you help.

Answer

Here is how you get -1 day for all days except for Monday ( which will now be -3 )

select DATEADD(DD, (case when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Monday' then -3 else -1 end ), CAST(GETDATE() as DATE))

If you need Saturday and Sunday to also show Friday:

select DATEADD(DD, (
case when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Monday' then -3 
     when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Sunday' then -2 
     when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Saturday' then -1 
else -1 end 
), CAST(GETDATE() as DATE))

Or if you need Saturday and Sunday to show the current day:

select DATEADD(DD, (
case when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Monday' then -3 
     when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Sunday' then 0 
     when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Saturday' then 0 
else -1 end 
), CAST(GETDATE() as DATE))