Date between Monday and previous day of week in TSQL

bildozer
bildozer used Ask the Experts™
on
I'm trying to pull all records between Monday and the day before.

For instance, on Thursday, I need the date range of Monday through Wednesday.

I've got a script that uses the following code:

 
SELECT * from dbo.events
WHERE dbo.events.eventdate BETWEEN DATEADD(wk,DATEDIFF(wk,7,getdate()),6) and getdate() 

Open in new window


This gives the range a start day of Sunday. How should I modify it to treat Monday as the start of the week?

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Avoid WEEK just in case the DATEFIRST setting affects it (I'm not sure).  Below code works accurately with any DATEFIRST and/or language settings.

For now, returns the same day if it's already a Monday, wasn't sure what you needed in that case.

SELECT e.*
FROM dbo.events e
WHERE e.eventdate >= DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7, GETDATE()) AND
    e.eventdate < CAST(GETDATE() AS date)
Database Specialist
Commented:
Set it in a query
SET DATEFIRST 1;
Value First day of the week is 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 7 (default, U.S. English) Sunday

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial