MSSQL Most optimal query for date range for a day, or day range

Viewed 50449

I wanted to find out what is the "best practices" approach to a query against a record set of datetime with a date (no time).

I use several queries that return records based on a date range, from a recordset that uses a datetime data type, which means each record needs to be checked using a between range.

Example of a query would be:

Select * 
FROM Usages 
where CreationDateTime between '1/1/2012' AND '1/2/2012 11:59:59'

I know using BETWEEN is a resource hog, and that checking a datetime data type of a date is always going to be very resource intense, but I would like to hear what others use (or would use) in this situation.

Would I get any type of performance increase converting the datetime record to a Date like:

Select * 
FROM Usages 
where CONVERT(DATE,CreationDateTime) between '1/1/2012' AND '1/2/2012'

Or possibly doing a check of less then / greater then?

Select * 
FROM Usages 
where (CreationDateTime > '1/1/2012') 
    AND (CreationDateTime < '1/2/2012 11:59:59')
2 Answers
Related