datetime comparison broken???   

I've spent a few hours researching the datetime comparison, and from what I 
understand, what I'm doing *should* be working... any help is GREATLY 
appreciated.

I have a table that stores a column (EventDateTime) in the datetime format.  
If I run a query in query analyzer, the column returns values like 2008-07-07 
16:11:19, so from what I understand, this is valid datetime format.  

I'm simply trying to filter my query results to only include events that 
happened within the last 7 days.  I've tried a half dozen different ways of 
doing this, but I always get items in the query results that are well over 7 
days old.  If I change the query to be items less than 7 days old, it seems 
to return the right values.  The results when trying to retrieve only items 
newer then 7 days consistently returns items that are years old.  For example 
I get '2006-05-20 16:11:19.000'

some things I've tried are (please note that when I have the "and" below, 
I'm just showing different WHERE statements that I've tried, NOT that I'm 
actually trying to combine them in one query :-))

<select statement> 
WHERE EventDateTime > dateadd("d",-7,getdate())

and 
WHERE EventDateTime > dateadd(dd, -7, getdate())

and 
WHERE EventDateTime > CAST(dateadd("d",-7,getdate()) AD datetime)

and 
WHERE convert(datetime,convert(varchar,EventDateTime,121)) > 
convert(datetime,convert(varchar,dateadd("d",-7,getdate()),121))

and
WHERE CONVERT(VARCHAR,EventDateTime,112) > 20080707

All of these consistently return (the same) items that are over the 7 days 
(as well as the items within the 7 day window).  I've even tried a few 
queries with the date set manually.  Oh, I also tried converting all of the 
values to VARCHAR for comparison, but no dice there either.  

If I just return the value of one entry 
SELECT EventDateTime from Events WHERE AutoID = '30888' I get
'2006-05-20 16:11:19.000'
and if I do 
SELECT getdate() I get 
'2008-07-07 19:39:21.640'

but if I filter with 
WHERE EventDateTime > getdate()
that 30888 entry (along with 7k others is returned... I'd expect nothing 
returned...

I'm pulling my hair out here.  From EVERYTHING I'm seeing, this should be a 
straight forward query, but I've spent HOURS on it.  Any help is GREATLY 
appreciated.

-Dan

date: Mon, 7 Jul 2008 16:49:11 -0700   author:   Dan

RE: datetime comparison broken???   

I am a **COMPLETE** IDIOT...
A few hours wasted... fun.. 

I had an OR statement in the WHERE test that I overlooked which caused the 
extra results to be shown....

"Dan" wrote:

> I've spent a few hours researching the datetime comparison, and from what I 
> understand, what I'm doing *should* be working... any help is GREATLY 
> appreciated.
> 
> I have a table that stores a column (EventDateTime) in the datetime format.  
> If I run a query in query analyzer, the column returns values like 2008-07-07 
> 16:11:19, so from what I understand, this is valid datetime format.  
> 
> I'm simply trying to filter my query results to only include events that 
> happened within the last 7 days.  I've tried a half dozen different ways of 
> doing this, but I always get items in the query results that are well over 7 
> days old.  If I change the query to be items less than 7 days old, it seems 
> to return the right values.  The results when trying to retrieve only items 
> newer then 7 days consistently returns items that are years old.  For example 
> I get '2006-05-20 16:11:19.000'
> 
> some things I've tried are (please note that when I have the "and" below, 
> I'm just showing different WHERE statements that I've tried, NOT that I'm 
> actually trying to combine them in one query :-))
> 
> <select statement> 
> WHERE EventDateTime > dateadd("d",-7,getdate())
> 
> and 
> WHERE EventDateTime > dateadd(dd, -7, getdate())
> 
> and 
> WHERE EventDateTime > CAST(dateadd("d",-7,getdate()) AD datetime)
> 
> and 
> WHERE convert(datetime,convert(varchar,EventDateTime,121)) > 
> convert(datetime,convert(varchar,dateadd("d",-7,getdate()),121))
> 
> and
> WHERE CONVERT(VARCHAR,EventDateTime,112) > 20080707
> 
> All of these consistently return (the same) items that are over the 7 days 
> (as well as the items within the 7 day window).  I've even tried a few 
> queries with the date set manually.  Oh, I also tried converting all of the 
> values to VARCHAR for comparison, but no dice there either.  
> 
> If I just return the value of one entry 
> SELECT EventDateTime from Events WHERE AutoID = '30888' I get
> '2006-05-20 16:11:19.000'
> and if I do 
> SELECT getdate() I get 
> '2008-07-07 19:39:21.640'
> 
> but if I filter with 
> WHERE EventDateTime > getdate()
> that 30888 entry (along with 7k others is returned... I'd expect nothing 
> returned...
> 
> I'm pulling my hair out here.  From EVERYTHING I'm seeing, this should be a 
> straight forward query, but I've spent HOURS on it.  Any help is GREATLY 
> appreciated.
> 
> -Dan

date: Mon, 7 Jul 2008 17:11:00 -0700   author:   Dan