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.