t-sql BETWEEN clause

Question

Does anyone know the range comparison of the BETWEEN clause? if I have a datetime datatype, does the BETWEEN clause compare until hour/minute/second level?

Answer 1

yes it does, if its the same (down to the millasecond) then it is valid and will assert to true. So will be shown

Answer 2

This:

WHERE datetime_column BETWEEN '2010-08-11' AND '2010-08-12'

is equivalent to

WHERE (datetime_column >= '2010-08-11 00:00:00.000' AND datetime_column <= '2010-08-12 00:00:00.000')

There are two things to note here:

1. This is true everywhere you use a datetime type. All datetime values include a time portion that's accurate and exact down to about 3 or 4 milliseconds, even if you didn't specify it. Entering a literal like '2010-08-11' doesn't mean you're checking on an entire day.
2. The range is inclusive on both ends - you keep the first instant of the last day as well, and so IMO it's not usually a good idea to use between with datetime types. This is especially bad if you have a column that only stores dates with zero values for the time, as you could include an entire extra day beyond what you intended.