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 ;

Answer 3

Create a Calendar Table if not exists

IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 
'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
   DROP TABLE [Calendar]
END

CREATE TABLE [Calendar]
(
   [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)

WHILE @StartDate <= @EndDate
BEGIN
      INSERT INTO [Calendar]
      (
            CalendarDate
      )
      SELECT
          @StartDate

         SET @StartDate = DATEADD(dd, 1, @StartDate)
END

Then, Use below query to get the output

declare @start datetime = dateadd(week, datediff(week, 0, getdate()), 0);
declare @end datetime = DATEADD(DAY,8,@start)

SELECT [CalendarDate]
FROM Calendar
WHERE CalendarDate BETWEEN @start AND @end