Extract all records last 7 days not incl today 1

[stillsteam]

Hello
MS SQL Server 2005

I have a datetime field that contains a date set to midnight (2003-03-03 00:00:00) and I need to extract all records between today minus 8 days to today minus 1 day ( last seven full days). I there an easy way to do this?

Thanks

/Jonas

 

[RiverGuy]

Are you familiar with the DATEADD function? Here's an example.

DECLARE @Date DATETIME
SELECT @Date = '20080701'
SELECT * 
FROM YourTable
WHERE TheDate BETWEEN DATEADD(day, -7, @Date) AND  DATEADD(day, -1, @Date)

 

[r937]

riverguy, that only works on july 1, 2008

can you show the BETWEEN using GetDate()?

;-)

r937.com | rudy.ca

 

[RiverGuy]

That's why I said example, meaning the poster could infer a complete solution to their problem from the logic. But yes, they could replace the hard coded July 1 with GETDATE() and strip off the time.

 

[r937]

that's my point... it's the "strip off the time" part that is the challenge to this problem, not the DATEADD part

r937.com | rudy.ca

 

[stillsteam]

Sorry
R937, you are dead on.
It is the timepart that is the challenge.
I've solved it using this code:
Case when Year(ofh.regdat) >= YEAR(DATEADD(dd,- 8, GETDATE())) AND Month(ofh.regdat) >= MONTH(DATEADD(dd, - 8, GETDATE())) AND Day(ofh.regdat) >= DAY(DATEADD(dd, - 8, GETDATE())) AND
Year(ofh.regdat) <= YEAR(DATEADD(dd, - 1, GETDATE())) AND Month(ofh.regdat) <= MONTH(DATEADD(dd, - 1, GETDATE())) AND Day(ofh.regdat)
<= DAY(DATEADD(dd, - 1, GETDATE()))
THEN 1 ELSE 0 end

...but I was looking for something nicer...

Thanks



/Jonas

 

[r937]

something nicer...

WHERE ofh.regdat >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-7,0)
AND ofh.regdat < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

r937.com | rudy.ca

 

[stillsteam]

Alot nicer...

Thanks


/Jonas