t-sql BETWEEN clause

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 l开发者_JS百科evel?

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

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.

It depends on the data type. BETWEEN returns TRUE if the value of the test_expression is greater than or equal to the value of the begin_expression and less than or equal to the value of the end_expression (Source).

The following:

BETWEEN date_field '2010-01-01 12:00:00' AND '2010-02-01 12:00:00'

is equivalent to this:

date_field >= '2010-01-01 12:00:00' AND date_field <= '2010-02-01 12:00:00'

Saying val BETWEEN @lowVal AND @highVal is exactly the same as saying @lowVal <= val AND val <= @highVal, so yes... datetime comparisons include all parts of the date. See here.