Get data between specific week days

Question

Get data between specific week days

giontech

Ten Centuries

Points: 1094
More actions
June 21, 2008 at 5:09 pm

#189473

Hello:
I need help in how to get data from a table with a daterange from sunday to saturday of the last week.
What I'm looking for is to be able to do this any day of the current week.
My problem here is that I can't use the dateadd function to decrease the days, because the days to decrease vary by the day I need to do the query.
Thanks 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

Can you give an example, or a few? It's not clear what you mean.

You also might want to check out datepart, which can get you different parts of a date, such as day of the week. And SET DATEFIRST to set the day on which your week starts.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 2

giontech (6/21/2008)
Hello:
I need help in how to get data from a table with a daterange from sunday to saturday of the last week.
What I'm looking for is to be able to do this any day of the current week.
My problem here is that I can't use the dateadd function to decrease the days, because the days to decrease vary by the day I need to do the query.
Thanks 🙂

I don't have any idea what you mean by that last part, but the following code will produce two dates... one for the Sunday from last week and 1 for the Saturday from last week... use them as criteria...

DECLARE @TestDate DATETIME

SET @TestDate = GETDATE()

SELECT DATEADD(wk,DATEDIFF(wk,-1,@TestDate),-1)-7 AS [Sunday from Last Week],

DATEADD(wk,DATEDIFF(wk,-1,@TestDate),-1)-1 AS [Saturday from Last Week]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)