Query Assistance for Newbie

I need to modify a query through which I've done mostly using the Query
builder in Enterprise Manager so it can be run periodically without manual
editing. The query builder creates the following text:

select [ATHSTATDATA].[NSTATEVENTID] as "Page or
Search",[ATHSTATDATA].[NDATA] as "Hits if Search", [ATHSTATDATA].[VCDATA] as
"Topic or Search String", [ATHSTATDATA].[DTTIMESTAMP] as "Time of Activity"
from [ATHSTATDATA]
where [ATHSTATDATA].[DTTIMESTAMP]<'4/30/2004 12:00:00 AM' AND
[ATHSTATDATA].[DTTIMESTAMP]>'4/23/2004 12:00:00 AM'
order by [ATHSTATDATA].[DTTIMESTAMP]

What I need to do is to replace the specific dates (<'4/30/2004 12:00:00
AM', '4/23/2004 12:00:00 AM') so that I get only rows created from the time
at which the query is run and back 7 days. Thanks in advance for whatever
help you can provide!...
WHERE dttimestamp
BETWEEN CAST(DATEDIFF(DAY,7,CURRENT_TIMESTAMP) AS DATETIME)
AND CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> ...
> WHERE dttimestamp
> BETWEEN CAST(DATEDIFF(DAY,7,CURRENT_TIMESTAMP) AS DATETIME)
> AND CURRENT_TIMESTAMP

WHERE dttimestamp
BETWEEN dateadd(DAY, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP

Would be somewhat less cryptic, and not rely on undocumented
conversion between integer and datetime.

Now, Ray said:

so that I get only rows created from the time at which the query is run
and back 7 days.

Which the above does on the hour, but Roy's sample query indicated that
he wanted it by calender day. In such we should write:

BETWEEN dateadd(DAY, -7, convert(char(8), CURRENT_TIMESTAMP, 112) AND
CURRENT_TIMESTAMP

This still not match the original query, as this had < and >, but
I don't really know what Ray means with today, I have to stop there.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp