What is wrong with this sql where-clause?

Question

I am selecting records based on two dates in a same column but my where condition fails to select records on the StartDate and EndDate...

where CreatedDate between @StartDate and @EndDate

I get only the records inbetween the dates and not the records on the StartDate and EndDate... Consider if I pass the same date as StartDate and EndDate I should be able to select all the records on that date. Any suggestions?

Answer 1

BETWEEN is inclusive. What are the dates you're specifying? Remember that DATETIME is accurate to the millisecond so if your dates are off by a millisecond then you won't pick up the "equal to" part.

You should be able to manually adjust @StartDate and @EndDate to do what you want like so:

SET @StartDate = CAST(FLOOR(CAST(@StartDate AS FLOAT)) AS DATETIME)
SET @EndDate = CAST(FLOOR(CAST(@StartDate AS FLOAT) + 1) AS DATETIME)

...
WHERE
   CreatedDate BETWEEN @StartDate AND @EndDate

Answer 2

see this question: http://stackoverflow.com/questions/749615/does-ms-sql-servers-between-include-the-range-boundaries; I think your problem may be the time as explained in this answer

Answer 3

From the MSDN page about BETWEEN:

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.

I would say that the @StartDate and @EndDate are probably not what you think they are - the DateTime datatype include hours, minutes, seconds and milliseconds and these should also be specified if you want consistent results.

See this answer for more detail.

Answer 4

Remember that SQL Server DATETIME includes the time component, and if you don't specify a time, it defaults to midnight -- and midnight is the first instant of that day.

In practical terms, if you said BETWEEN '6/3/2010' AND '6/4/2010' then the only records from 6/4 you'll see are those that occurred on 6/4/2010 at 00:00:00. If you've got a record on 6/4 at 12:34 it won't be returned.

The best fix is probably to (A) use explicit > and <; and (B) use date math to get the right endpoint if you're only using dates without times. E.g.:

WHERE order_date >= @StartDate
AND   order_date <  CAST(FLOOR(CAST(@StartDate AS FLOAT)) AS DATETIME) + 1