MSSQL Most optimal query for date range for a day, or day range
datetime, range, sql, sql-server
Solution
What you think you know is not correct.
Neither using `BETWEEN` or DATETIME data types is a resource hog.
Provided that you index the column, that the column really is a DATETIME and not a VARCHAR(), and that you don't wrap the field in a function, everything will be nice and quick.
That said, I would use `>=` and `<` instead. Not for performance, but logical correctness.
WHERE
myField >= '20120101'
AND myField < '20120102'
This will work no matter whether the field contains hours, minutes, or even (with a mythical data type) pico seconds.
With an index on the field it will also give a range scan.
You won't get any faster. No tricks or functions needed.
Problem
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') ```