selecting records for the last 7 days 2

[nathanharcup]

Hi All,
Hope you can help - I have a query where i want to count records in a table where a date column's day is in the last 7 days, been trying to do it with datepart but can't seem to get it to function.

Can anyone give me an example?

Thanks

 

[RiverGuy]

SELECT *
FROM YourTable 
WHERE DateColumn BETWEEN 
DATEADD(day, -6, CONVERT(DATETIME, (CONVERT(CHAR(8), GETDATE(), 112))))
AND 
CONVERT(DATETIME, (CONVERT(CHAR(8), GETDATE(), 112)))

 

[gmmastros]

To start....

Select Count(*)
From   YourTable
Where  DateColumn >= GetDate()

The GetDate() function returns date and time. If your values do not have a time component (i.e. all times are midnight), then you may want to try this:

Select Count(*)
From   YourTable
Where  DateColumn >= dateAdd(Day, DateDiff(Day, 0, GetDate()),0)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry. I made a mistake.

Select Count(*)
From   YourTable
Where  DateColumn >= GetDate() - 7

Select Count(*)
From   YourTable
Where  DateColumn >= dateAdd(Day, DateDiff(Day, 0, GetDate()),0) - 7

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nathanharcup]

thanks guys i have used gmmastros's example but have played with all

cheers for the quick replies as always

Nathan