sql server get last previous date excluding weekend-CodePudding

I am using this query to get last previsous date.

SELECT * 
FROM MyTable
WHERE
CAST(MyTable.DATE AS DATE) = DATEADD(day, -1, CAST(GETDATE() AS date))

I want to exclude weekends and from today should give yesterday, or on Monday, will give the previous Friday

CodePudding user response：

You could check for today being 'Monday' and subtract 3 days, otherwise subtract 1:

SELECT DATEADD(
    day,
    IIF(DATENAME(weekday, GETDATE()) = 'Monday', -3, -1),
    CAST(GETDATE() AS DATE)
)

CodePudding user response：

This is easy to solve with a calendar table, and doesn't rely on @@language or @@datefirst settings:

DECLARE @PreviousWeekday date;
SELECT @PreviousWeekday = MAX(TheDate)
  FROM dbo.TheCalendar
  WHERE IsWeekend = 0
    AND TheDate >= DATEADD(DAY, -4, GETDATE());

SELECT <column_list>
  FROM dbo.MyTable
  WHERE [DATE] >= @PreviousWeekday
    AND [DATE] <  DATEADD(DAY, 1, @PreviousWeekday);

As a bonus, that where clause can use an index on [DATE] with the right estimates, and the calendar table makes it easy to adjust later if you decide you want to skip holidays or other work outages that are technically weekdays but not work days (just add AND IsHoliday = 0).