Search code examples
sql-server-2012dateadd

Getting “before two days” date sql server

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.

Solution

  • 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.