sql two monday night between

Question

I have a problem about sql query. Now I have GETDATE() FOR example today is wednesday, I need to have date between tho monday nights. and GETDATE() will be in this two date

Example today is thursday 18.05.2017 I want date between 15.05.2017 and 22.05.2017

I couldn't find any solution. How can I write it in where statement in query.

SELECT * FROM MATCHES
WHERE ...

Thanks in advance

Answer 1

To receive first monday: SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)

and second one: SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()) + 1, 0)

http://joelabrahamsson.com/getting-the-first-day-in-a-week-with-t-sql/

also

Get first day of week in SQL Server

Answer 2

here is the solution firstly create a calendar table then

declare @startDate datetime = dateadd(week, datediff(week, 0, getdate()), 0);
declare @endDate datetime = DATEADD(DAYS,7,@startDate)

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= @startDate 
AND     Date < @endDate ;