CODEWITHSUNDEEP
sqlsql-server-2005
challengeAccepted
challengeAccepted

Reputation: 7590

Get records from the week until the given date

How can i get records in SQL Server 2005. Input will be a weekday Date. Output should be all the records from that week before that date. For Example, If i give a Wednesday Date, i should get all the records from Monday until Wednesday of that week.

I have tried this to get all the records in a specific week when a weekday is given as input. But i want records until the given day from Monday. 

where RD.Date > DATEADD(dd, -(DATEPART(dw, @Date)-1), @Date) and RD.Date < DATEADD(dd, 7-(DATEPART(dw, @Date)), @Date)

Thanks for the help in advance!!

Upvotes: 1

Views: 134

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460058

This should work:

SET DATEFIRST 1
...
WHERE RD.Date >= DATEADD(dd, (-1) * (DATEPART(dw, @Date) - 1), @Date)
AND   RD.Date < DATEADD(dd, 1, @Date) -- inclusive

DATEPART(dw, @Date) returns the day-number in the week, so for wednesday it returns 3. But since you want to include the monday i needed to subtract 1.

MSDN according to the comment of Lamak:

When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST.

Upvotes: 2

Tiny Haitian
Tiny Haitian

Reputation: 479

How about this?

 WHERE RD.Date BETWEEN DATEADD(wk, DATEDIFF(wk,0,@Date), 0) AND @Date

I know in SQL Server, BETWEEN forces an inclusive search, so you can do a > and < against RD.DATE

Upvotes: 3

Related Questions