October 12, 2004 at 10:18 am
i am writing a report that needs to do the following:
show all records in a table that have a removed date in a column within the last 7 days.
every time the report runs it should only return those records that have a removal date within the last 7 days
any help would be appreciated
October 12, 2004 at 10:29 am
how about WHERE CONVERT(VARCHAR(10), Removed, 101) >= DATEADD(DAY, CONVERT(VARCHAR(10), GETDATE(), 101)
as an example??
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 13, 2004 at 3:26 am
or
WHERE DATEDIFF(day,RemovedDate,GETDATE()) < 7
Far away is close at hand in the images of elsewhere.
Anon.
October 13, 2004 at 6:56 am
Depends on what you mean by last 7 days.
Starting at the time it is run, if so then
RemovedDate >= dateadd(d,-7,getdate())
If starting at midnight time and including that day as day 1 then
RemovedDate >= dateadd(d,datediff(d,0,getdate()) - 6,0)
If you want the last 7 days and include day ran without regard to time then
RemovedDate >= dateadd(d,datediff(d,0,getdate()) - 7,0)
If you want the last 7 days without including day ran then
RemovedDate BETWEEN dateadd(d,datediff(d,0,getdate()) - 7,0) AND dateadd(ms,-3,dateadd(d,datediff(d,0,getdate()),0))
Note: On the last example I used milliseconds to offset from Midnigth same day as I don't know your precision. If you do in minutes you can adjust or leave as is.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply