SQL - Get all dates between Current day and start and end of week

Question

I am trying to set the start day to Saturday

SET DATEFIRST 6 -- Sets start day to Saturday

And then get all the GameDate between the start of the week and the end of the week. But it needs to be done with the current day. For instance: if the current day is thursday and the end of the week is Friday I don't want to do this:

DECLARE @StartWeek datetime Set @StartWeek = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), -5))
DECLARE @EndWeek datetime Set @EndWeek = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), 1))

Because this will make it that the next 5 days will get shown. And If the CurrentDay is on Thursday I don't want the next 5 days to get shown. Just the GameDates from every week.

This is what I got so far:

DECLARE @DateTable Table (DateofWeek Date) -- Creates table
DECLARE @DateToday Date SELECT DAY(GETDATE()) 'Current Day' -- Gets current date

DECLARE @StartWeek datetime Set @StartWeek = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), -5)) --This is the part thats wrong
DECLARE @EndWeek datetime Set @EndWeek = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), 1)) --This is the part thats wrong

SET DATEFIRST 6 -- Sets start day to Saturday


SELECT DATEDIFF ( DAY, @DateToday, @EndWeek) AS Diffrence_End
SELECT DATEDIFF ( DAY, @DateToday, @StartWeek) AS Diffrence_Start
    FROM @DateTable

WHILE @DateToday >= @EndWeek AND @DateToday <= @StartWeek -- Shows all gameDates between StartWeek and Endweek
BEGIN   
    SELECT DATEDIFF ( DAY, @DateToday, @EndWeek)
    Insert Into @DateTable



SELECT * 
    FROM @DateTable
END;

Some of the Query is probably wrong, especially the last part.

I hope I made myself clear enough, if there are any questions please don't hesitate to ask me!

Answer 1

Have a look:

-- Sets start day to Saturday
SET DATEFIRST 6 

-- Creates table
DECLARE @DateTable Table ([DateofWeek] date); 

-- StartDate = DATEPART(DW, ... ) = 1
DECLARE @StartDate date; 
SET @StartDate = DATEADD(day, (DATEPART(dw, GETDATE()) - 1) * -1, GETDATE());

-- EndDate = StartDate + 6 days
DECLARE @EndDate date;
SET @EndDate = DATEADD(day, 6, @StartDate);

-- Generates table values
DECLARE @CurrentDate date;
SET @CurrentDate = @StartDate;

WHILE @CurrentDate <= @EndDate
BEGIN
    INSERT INTO @DateTable ([DateofWeek]) VALUES (@CurrentDate);
    SET @CurrentDate = DATEADD(day, 1, @CurrentDate);
END

--Check it
SELECT *, DATENAME(dw, DateOfWeek) as Name FROM @DateTable;

GO

DateofWeek          | Name     
:------------------ | :--------
18/03/2017 00:00:00 | Saturday 
19/03/2017 00:00:00 | Sunday   
20/03/2017 00:00:00 | Monday   
21/03/2017 00:00:00 | Tuesday  
22/03/2017 00:00:00 | Wednesday
23/03/2017 00:00:00 | Thursday 
24/03/2017 00:00:00 | Friday   

dbfiddle here