0

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.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
OnThaRise
  • 117
  • 1
  • 1
  • 9
  • Does this answer your question? [How to get Previous business day in a week with that of current Business Day using sql server](https://stackoverflow.com/questions/9922756/how-to-get-previous-business-day-in-a-week-with-that-of-current-business-day-usi) – PM 77-1 Aug 03 '22 at 21:07
  • It has some truth to what I'm looking for but Ivar Harris' answer below is more convenient for my problem. Thank you for the referenced link though. – OnThaRise Aug 04 '22 at 14:24
  • If you are satisfied with @IvarHarris answer, make sure to mark it as "Accepted". – PM 77-1 Aug 04 '22 at 14:32

1 Answers1

1

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))
Ivar Harris
  • 156
  • 3
  • This seems to be working, thank you for that! I will be able to confirm on Monday if all good :) Will check it as Correct Answer once I can confirm the data. Thank you and have a good day! – OnThaRise Aug 04 '22 at 14:22