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.

Answer 2

I learned this technique from SQL Server Magazine February 2007 (Datetime Calculations by Itzik Ben-Gan). This way, your 'between' will work regardless of whether the row's date is after midnight, as everything has been normalized to be at midnight with this comparison:

select *
from someTable
where dateadd(day, datediff(day, 0, somedate), 0) between '07/26/2009' and '07/26/2010' 

The datediff and dateadd work together to strip off the time and leave the date. You can then compare it to string literals, or other dates that have had the same modification done. I'd recommend putting this in a function.

EDIT: Based on OMG Ponies' comments. This will not take advantage of an index on the date column. An alternative might be to use the time stripping technique in addition to the technique others have mentioned. So instead of doing it on the table's column, do it on the last argument to 'between'. You could have a function like so:

CREATE FUNCTION [dbo].[fn_enddate](@enddate datetime)
RETURNS datetime AS  
BEGIN
    DECLARE @endOfDay datetime
    set @endOfDay = dateadd(millisecond, -2, dateadd(day, datediff(day, 0, @enddate) + 1, 0))
    return @endOfDay
END

This takes the argument date, sets it to midnight the next day, then subtracts two milliseconds, giving the end of the day of the given datetime. So then you could do:

select *
from someTable
where somedate between '07/26/2009' and dbo.fn_enddate('07/26/2010')