Date between Monday and previous day of week in TSQL
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:
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.
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()
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial