Use BETWEEN keyword with caution when comparing dates


If you’re filtering results with a clause involving a date column being between two given dates, it can be safer to define the clause as

MyTable.MyDate >= @Date1 AND < @Date2

I discovered an interesting trap the other day when diagnosing an issue where an SSRSSQL Server Reporting Services report was returning data outside of the given date parameters.
It is important to note that the DATETIME column being used did not specify a time (e.g. 2014-01-10 00:00:00).

So, here’s an example.

  1. You have a record with a PaymentDate value of 2014-10-10 00:00:00.
  2. You have another record with a PaymentDate value of 2014-10-11 00:00:00
  3. You enter the values 2014-10-10 00:00:00 and 2014-10-11 00:00:00 as your start and end date parameters, respectively

So, based on this you could expect both results to be returned. That is because the end date matches one of the payment date values.

Therefore, changing the where clause to a similar format below can solve the problem.

MyTable.MyDate >= @Date1 AND < @Date2

Hope that helps.