Search code examples
sqlsql-servert-sqldatediffdateadd

SQL setting start date to Friday, and read Data between 2 dates

I'm trying to set the start date to friday on a sql query. Which I have done as you can see below. What I need to do now is show all the GameID's between Friday and Saturday, and it refreshes every week (so that every week it shows other games that have been played in that week).

I'm a complete beginner at SQL so any help is greatly appreciated!

I have tried the sql query below.

DECLARE @StartFriday datetime
DECLARE @EndSaturday datetime

SET DATEFIRST 6 -- Set the start of the week to Friday

SELECT *
FROM
(

SELECT  GameDate,
        DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), -5)) AS 'StartFriday', 
        DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), 1)) AS 'EndSaturday'

FROM VW_Resultaat_Score 

WHERE GameDate BETWEEN 'StartFriday' AND 'EndSaturday' --Show all GameDates between @StartFriday and @EndSaturday
)

I would love any help I can get!

Cheers

Solution

  • Perhaps this will work better:

    DECLARE @StartFriday datetime
DECLARE @EndSaturday datetime

SET DATEFIRST 6
Set @StartFriday = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), -5))
Set @EndSaturday = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), 1))

SELECT *
FROM
(
SELECT  GameDate
FROM VW_Resultaat_Score 
WHERE GameDate BETWEEN @StartFriday AND @EndSaturday
)