Someone asked me if "BETWEEN" is inclusive or exclusive; it is inclusive. BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
Give the following:
SELECT OrderID, OrderDate
FROM [Order]
OrderID OrderDate
1 2008-10-17 09:30:00.000
2 2008-10-17 10:00:00.000
The following select returns both records:
SELECT OrderID, OrderDate
FROM [Order]
WHERE OrderDate BETWEEN '2008-10-17 09:00:00' AND
'2008-10-17 10:00:00'
The equalviant GT/LT select for inclusive:
SELECT OrderID, OrderDate
FROM [Order]
WHERE OrderDate >= '2008-10-17 09:00:00' AND
OrderDate <= '2008-10-17 10:00:00'
To make an exclusive select use > & <, notice the end point is not included. The 10AM record is not included in the output set.
SELECT OrderID, OrderDate
FROM [Order]
WHERE OrderDate > '2008-10-17 09:00:00' AND
OrderDate < '2008-10-17 10:00:00'
OrderID OrderDate
1 2008-10-17 09:30:00.000
Date/Time Defaults:
Interesting thing - when the time part is unspecified, it defaults to midnight (00:00:00).
OrderID OrderDate
1 2008-10-17 00:00:00.000
2 2008-10-18 00:00:01.000
SELECT OrderID, OrderDate
FROM [Order]
WHERE OrderDate BETWEEN '2008-10-17' AND
'2008-10-18'
Result is only row 1; between picks up to 2008-10-18 00:00:00.000 (inclusive) thus not including the one second past midnight.
OrderID OrderDate
1 2008-10-17 00:00:00.000