rbarryyoung (11/25/2008)
I think that Vladan's "-7" should probably be "+7", but I am not sure
That depends whether you need data from the last 7 days (like if you need to find what projects were finished last week), or from the next 7 days (beginning today and going into future... like if you make a list of goods that should be delivered during next week).
My query is for the last 7 days. It is different from the one posted by Peso, because I subtract 7 days from today and Peso adds 7 days to the date stored in a row. Otherwise they are almost identical.
In my opinion, it is better for performance to subtract 7 days from a parameter (here: GETDATE()) once for the whole query, than to add 7 days to every row in the table.
I tested this on a large table in my DB.
SELECT count(*)
FROM table T
WHERE T.date > dateadd(dd,-7,GETDATE())
uses index seek and is immediate, while
SELECT count(*)
FROM table T
WHERE GETDATE() > dateadd(dd,7, T.date)
uses index scan and takes some 3 seconds.
The difference is negligible on small tables, but if you want to write well-performing SQL, this is one of the things that can be helpful.