Scott D. Jacobson (8/7/2012)
You should NEVER use BETWEEN with datetime data.
Do you have any more material on this or is it purely anecdotal? For instance, what's wrong with this?
SELECT Col1,Col2,Col3 FROM SomeTable WHERE SomeDateColumn BETWEEN '8/7/2012' AND '8/8/2012'If my business day starts at 8 AM and ends at 8 PM, then I get everything for today right? Of course, I'd prefer a variable or a parameterized stored procedure in practice but "NEVER use BETWEEN with datetime data" seems kind of absolute.
What about this? Many of our reporting queries resolve out to this when a between is chosen in the application:
SELECT Col1,Col2,Col3 FROM SomeTable WHERE SomeDateColumn BETWEEN '8/7/2012' AND '8/7/2012 23:59:59.999'
Run the following:
select cast('8/7/2012 23:59:59.999' as datetime)
The result returned for me is 2012-08-08 00:00:00.000. This means that using between in this case would actually return any records where SomeDateColumn contained the value 2012-08-08 00:00:00.000. In this case, what you really want is this:
SELECT
Col1,Col2,Col3
FROM
SomeTable
WHERE
SomeDateColumn >= '8/7/2012' AND -- should use '20120807'
SomeDateColumn < '8/8/2012'; -- should use '20120808'