SQL Date Compare by only using Date not Time

Question

This was originally going to be a question about how to implement this because I was stuck at a certain part but I am now curious as to why this was happening in the first place. I needed to compare only the dates not the time which wouldn't have been a problem if the times didn't differ. The code below shows the query I was originally trying

SELECT *
FROM Employee e
inner join OT_Hours o on o.Emp_ID=e.Emp_ID
inner join Position p on p.Position_ID=e.Position_ID
inner join Signup_Sheet s on s.Employee_ID=e.Emp_ID
WHERE e.Eligible_OT=1 and s.Day_Shift = 1 
and p.Position_Name = 'Controller' 
and Convert(Varchar(20),s.Date,101) = '07/26/2010'
and Convert(Varchar(20),o.Date,101) <='07/26/2010'
and Convert(Varchar(20),o.Date,101) > '07/26/2009'
and o.Quantity NOT IN(0.3) order by o.Date DESC

I would get no result when I ran that query, but when I removed the second last line it would return 12 results (<=) and when I removed the 3rd last line but kept the second last it would return 6 results (>). After reviewing the data I could see that 4 of those results should have been returned. Now for the wierd part. Below is the code I am currently using.

SELECT DISTINCT o.Date, e.Emp_ID as Emp_ID, e.First_Name+ ' ' +e.Last_Name as Name, o.Quantity as Sum
FROM Employee e
left join OT_Hours o on o.Emp_ID=e.Emp_ID
left join Position p on p.Position_ID=e.Position_ID
left join Signup_Sheet s on s.Employee_ID=e.Emp_ID
WHERE e.Eligible_OT=1 and s.Day_Shift = 1 
and p.Position_Name = 'Controller' 
and Convert(Varchar(20),s.Date,101) = '07/26/2010'
and o.Date between '07/26/2009' and '07/26/2010'
and o.Quantity NOT IN(0.3) order by o.Date DESC

This query will return results but I also tested it like I did the other one when o.Date was above and below the date specified. When the date was <= 16 results were returned, when > 8 results were returned. The final query produced 6 results. Now this is not a production database I'm querying against and I'm the only one using it so the data did not change. Any explanation on why this was happening? I'm assuming it had something to do with converting it to varchar and it couldn't compare properly but that doesn't explain why I would get 12 <=, 6 > and then no results in the end. Also if anyone knows a better way to implement this please let me know.

Answer 1

The two queries aren't the same - this:

and o.Date between '07/26/2009' and '07/26/2010'

...is the equivalent of:

and o.Date >= '07/26/2009' 
and o.Date <= '07/26/2010'

BETWEEN is ANSI standard, and inclusive on every database I've ever encountered.

Mind that if you don't specify a time portion for DATETIMEs, the value defaults to starting at midnight of the day - 00:00:00.