CODEWITHSUNDEEP
sqlsql-servert-sql
user384080
user384080

Reputation: 4692

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 level?

Upvotes: 3

Views: 2205

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

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.

Upvotes: 4

mattmc3
mattmc3

Reputation: 18325

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.

Upvotes: 1

Daniel Vassallo
Daniel Vassallo

Reputation: 344291

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'

Upvotes: 2

Amjid Qureshi
Amjid Qureshi

Reputation: 602

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

Upvotes: 5

Related Questions