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

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.

There are several considerations regarding dates.

First, you want to be sure that relevant indexes get used. In general, this means avoiding functions on the column. This applies to data types other than dates, but functions a prevalant for understanding dates. So, CONVERT() is a bad idea from a performance perspective, assuming that the column is indexed.

Second, you want to avoid unnecessary conversions between formats. So, a call to a function must happen for every row. Instead, converting a constant string to a date/time happens once at compile time. The first is less efficient. Another reason to avoid CONVERT(). However, in many queries, other processing (such as joins) is far more time-consuming than conversions, so this may not be important.

As for the choice between "between" and signed operations. The better practice is to use "<" and ">" and ">=" and "<=". It makes the logic clearer for dates and doesn't have an issue with things like seconds being accurate to 3 ms.

As far as I know, between on dates works as efficiently using indexes as other types of fields. However, for accuracy and portability it is best to do the individual comparisons.

So, the third version would be preferred.