Yesterdays Date (most of the time)

Hello everybody,
I have the following code in SQL server so that the query returns results where the date is yesterday. (End Date is the name of the column in the Events table)
FROM Events

This seems to work in the way I intended but I now need to make some modifications. If the day is Monday, I need to return the previous 3 days dates in the result (EG the dates for Friday, Saturday and Sunday). If it is any other day of the week I need the result to be yesterday's date.

Does anybody know how to do this in SQL?

Many Thanks


Case Statement

Please google try

Using a function on a column prevents SQL Server from utilization an index on that column if it is available. The better way to query datetime columns is to utilize an open-interval range check. This is written using greater than or equal to - and less than...

WHERE column >= {start of interval}
AND column < {one more than end of interval}

Using variables - we can calculate the start and end dates using the following:

DECLARE @startDate datetime = dateadd(day, datediff(day, 0, getdate()) - 1, 0)  --default to yesterday at midnight
      , @endDate datetime = cast(getdate() as date);  --current day at midnight

    SET @startDate = iif(datename(weekday, getdate()) = 'Monday', dateadd(day, -2, @startDate), @startDate)

   FROM Events
  WHERE EndDate >= @startDate
    AND EndDate <  @endDate;

Hi Jeff,

Thanks for your response.

I am quite new to SQL. Do you need administrator access to use variables? I do not have admin access in my work environment.


No - you do not need admin access to use variables. It also depends on what tool you are using to access SQL Server. If you are using SSMS then you can do the above with no issues.

Hi Jeff,

This seems to work perfectly in SSMS.

Thank you for taking the time to help me.

Best Wishes

Big Vinnie