Filter query result for every Thursday of a given month-CodePudding

I have a SQL table with 2 columns ID (int) and CreateDate (DateTime) type. I want to filter all the IDs for each Thursday for a given month whether I pick Jan, Feb, March, etc...

CodePudding user response：

If I understand correctly, DATEPART function might help you to do that.

you can use weekday in the first parameter which returns weekday from DateTime, if you want to compare others information DATEPART might help you compare.

SELECT *
FROM T
WHERE  
    DATEPART ( weekday , CreateDate ) = 5

CodePudding user response：

Start by filtering on the month/year date range first, to reduce the number of results and keep things sargable. Then use datePart to identify the Thursday's within the selected range.

db<>fiddle

-- Assumes SET DATEFIRST 7 
SELECT * 
FROM   YourTable
WHERE  CreateDate >= '2022-01-01'
AND    CreateDate < '2022-02-01'
AND    DatePart(dw, CreateDate) = 5

Note, the results of Datepart(dw) may differ depending on your @@DATEFIRST setting. A deterministic version of the query would be:

SELECT *
FROM   YourTable
WHERE  CreateDate >= '2022-01-01'
AND    CreateDate < '2022-02-01'
AND    ((DatePart(dw, CreateDate)   @@DATEFIRST-1) % 7 1) = 5