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')