help with... {fn NOW() } -7 to find past 7 days

[Mitchell_Collen via AccessMonster.com]

I want to create a view that will show me the past 7 days when from the date
that the query is executed.
I have tried these but I continue to find more than 7 days.

EnteredDate >= { fn NOW() } - 7

also tried

EnteredDate >= {fn getDate() } -7

Will you help me? Do you think that I need to use datepart and pull out day?
If I do it that way then I don't know how to use the function. So if you know
how to do it that way will you send me the right syntax?

Thanks, Misty

 

[Sylvain Lafontaine]

More than seven days? A problem here with the time part or the way your
dates are stored. However, as you didn't provide any detail or example
about the way you are storing your dates and how exactly you are using this
value ans what exact result you are expecting (does substracting one day
corresponds to exactly 24 hours?); it's impossible to tell you what's going
wrong in your query.

Finally, it's probably a better idea to use the regular T-SQL function
getDate() than to use the (very) old ODBC call { fn now() }.

 

[Mitchell_Collen via AccessMonster.com]

Sylvain,

My dates are stored timestamp in military hours. I guess I can just play with
the getDate() function until it looks about right. I just think that there
must be something to pull out 7 days. Do you think it is possible to break it
down to days and then count to 7?

EnterTime:
03/03/2008 13:10:01

SELECT COUNT(drug) AS Total, EnterDate
FROM OrdState
WHERE (EnterTime >= { fn NOW() } - 7)
GROUP BY EnterTime,

 

[Sylvain Lafontaine]

What do you mean exactly by "stored timestamp in military hours"? Are you
using a datetime column or something else like a char or a varchar? In your
case, you still don't show us an example of something that is wrong (ie, a
date that should be excluded from the resultset but is not) and yes, it's
possible to break down the datetime to a date without a time but again, if
you don't tell us what's wrong, it's hard to give you an advice on how to
solve your problem.

declare @dt datetime, @dtnow datetime
set @dt = '03/03/2008 13:10:01'
set @dtnow = '03/10/2008 12:10:00' -- Simulate now() or today.
select @dt, @dtnow, @dtnow - 7, Case when @dt >= @dtnow -7 then 1 else 0 end

set @dtnow = '03/10/2008 14:10:00' -- Again, a simulation of now() but
later in the day.
select @dt, @dtnow, @dtnow - 7, Case when @dt >= @dtnow -7 then 1 else 0 end