Today, I had to fix a bug in a stored procedure that was not handling dates correctly. A date column was being compared to the current date. However, the current date also contains hour, minute and second values whereas the time part of the date column always contained 00:00:00. In other words, we did not want the time to be relevant in the comparison. I found the best solution to get rid of the time component on this blog in the readers’ comments: http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm

The solution is to convert the dates in number of days as follows:

datediff(d, 0, date1) < datediff(d, 0, date2)

In our case, we had the replace a where clause looking like this:

WHERE StartDate <= GETDATE() AND EndDate >= GETDATE()

by the following:

WHERE StartDate <= GETDATE() AND datediff(d, 0, EndDate) >= datediff(d, 0, GETDATE())

Note that it is not important to use the same method for the start date because the time component of the StartDate column is always 00:00:00.