0

I want to get two days before from current date and that date should not be Saturday and Sunday today is 1st July 2021 so i need to get data for 30th jun 2021 and 29th jun 2021 and that day should not be Saturday and Sunday.

Ram
  • 727
  • 2
  • 16
  • 33
  • 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) (the logic is *identical* apart from you are subtracting 2 days from the current date first). – Thom A Jul 01 '21 at 10:58

1 Answers1

1

Use a CASE expression:

SELECT CASE DATEPART(dw, GETDATE())
       WHEN 2 THEN DATEADD(day, -3, GETDATE())  -- Monday becomes Friday
       WHEN 3 THEN DATEADD(day, -4, GETDATE())  -- Tuesday becomes Friday
       ELSE DATEADD(day, -2, GETDATE())         -- otherwise roll back 2 days
       END;

The above answer assumes that server settings place Sunday as day 1, Monday as day 2, ..., and Saturday as day 7. Without this assumption my answer might be worthless.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Worth nothing this *assumes* the user's `@@DATEFIRST` setting. As an Englishman, for example, this incorrectly returns `2021-06-26` if the date were `2021-06-29`. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=26e0ab7b0986404a16020bc5c7cdb53f) – Thom A Jul 01 '21 at 11:02