query to fetch records of last 7 day inclu....

Question

Viewing 10 posts - 1 through 9 (of 9 total)

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

brij_rauthan SSCommitted Points: 1521 More actions · Answer 1

is this from single table and do that table have date column?

Brij

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Select * From TblName where <Date_Column> >=DateAdd(day,DateDiff(day,0,getdate()),-6)

or

Select * From TblName where <Date_Column> >DateAdd(day,DateDiff(day,0,getdate()),-7)

N 56°04'39.16"
E 12°55'05.25"

Daizy_online Grasshopper Points: 10 More actions · Answer 3

little simplified...

select * from tblname WHERE datecol>= DATEADD(DAY,-7,GETDATE())

Or

SELECT * FROM tblnameWHERE datecol BETWEEN dateadd(DAY,-7,GETDATE()) AND dateadd(day,1,GETDATE())

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

The original posting was inserted in last 7 days including today.

N 56°04'39.16"
E 12°55'05.25"

brij_rauthan SSCommitted Points: 1521 More actions · Answer 5

Daizy

Further your query will subtract only 7 days from current datetime, so if query is run at 10AM, then you will not get the records till 10 AM.

thanks

Brij

Daizy_online Grasshopper Points: 10 More actions · Answer 6

Grasshopper

Points: 10

June 14, 2006 at 6:23 am

Yup!!

Missed the time issue.

Thanks

Daizy

David Burrows SSC Guru Points: 65142 More actions · Answer 7

WHERE DATEDIFF(day,[Date],GETDATE()) <= 7

but this will not utilise any index on the date column

whereas Peters solution will

Far away is close at hand in the images of elsewhere.
Anon.

DAVNovak Hall of Fame Points: 3120 More actions · Answer 8

I find in this situation, I like to use a variable for the cutoff day:

DECLARE @dtStart datetime
SET @dtStart = CONVERT(varchar(12), DATEADD(day, -8, GETDATE()), 101) + ' 23:59:59'

SELECT ...
FROM ...
WHERE {date column} > @dtStart

This make sure that you get everything for the past seven days regardless of the time. Also, if you have to reuse it multiple times, you do not have to retype the formula each time.

Dave N

David Burrows SSC Guru Points: 65142 More actions · Answer 9

The only problem is with milliseconds for times of '23:59:59.003' to '23:59:59.997' which if present on day-8 then your query would include it.

Therefore you would to use CONVERT(varchar(12), DATEADD(day, -8, GETDATE()), 101) + ' 23:59:59.997'

or as I prefer how to do it

SET @startdate = DATEADD(day,DATEDIFF(day,0,GETDATE())-7,0)

...

WHERE [Date] >= @startdate

Far away is close at hand in the images of elsewhere.
Anon.