DateTime Craziness in SQL Server when looking for records within a date range

Working with the DataTime datatype in SQL Server can be very frustrating at times Especially, when you use them in your where clause. For example If you want to list all records within a certain date range, you might end up retrieving incorrect rows.

Select * from TableName where CreatedDate = ‘2010-05-20’
The above query will return only those rows from the table which is exactly as following in the table.
2010-05-20 00:00:00.000
What about other rows which were created on the same day but have a different time part like the following:

2010-05-20 02:23:33.000

2010-05-20 05:44:55.000
The above records even though it was created on the same day will not be returned by our above query because the time part does not match. This can be frustrating if you are not aware of it.
So what can we do if we want to return all the records created on a day or within a date range, no matter at what time they were created during those dates.
There are different ways of doing it.
1.       Using Functions on the Table Columns
There are various date functions which can be used to convert the column having datetime to just the date before using it in the Where clause.
 
Eg.
Select * from TableName where
(Cast(Floor(Cast(CreatedOn as Float)) As DateTime) BETWEEN @StartDate AND @EndDate))
The above example converts the CreatedOn Column to just contain the Date part  and then used in the Where clause to check for the dates in the Between Clause.
The above query should work fine and also there are other functions you can use on the Table columns. But one drawback of using functions on the table column is that it will not use the index if the column has indexes on it, and thus can hamper the performance very much. So there is another way to do the same thing as shown below
2.       Using Functions on the variables instead of the table column as follows.
Select * from TableName Where(CreatedOn >= @StartDate AND CreatedOn < DateAdd(d,1, @EndDate))
What I am doing in the above query is instead of using the function on the table column, I am using the DateAdd function on the variable @EndDate.
In the above query I am using the >= for the StartDate that will return all the records created within the date range including the StartDate. And why am I using the DateAdd function to add a day to the EndDate. If I do not do that, and if I select for a date range with both the StartDate and EndDate same, the query does not return correct records, so to avoid that, I just add 1 day to the EndDate and I do that using the DateAdd function and it works fine.
As you see I am using the function on the variable and not on the table column, this is more efficient if my column has indexes on it.
 
References: