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?

I assume this is a date/time column. Could the times be throwing you off (i.e. between 6/3/2010 0:00:00 and 6/3/2010 0:00:00). Nothing would come back in this case. — Tommy, Jun 04 '10 at 05:46

score 3 · Answer 1 · edited May 23 '17 at 12:19

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.

Dean Harding · Answer 2 · 2010-06-04T05:52:52.837

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

edited Jun 04 '10 at 05:52

answered Jun 04 '10 at 05:41

Dean Harding

71,468
13
145
180

Why? This is exactly what between does – gbn Jun 04 '10 at 05:43

score 1 · Answer 3 · edited May 23 '17 at 12:19

1

see this question: Does MS SQL Server's "between" include the range boundaries?; I think your problem may be the time as explained in this answer

edited May 23 '17 at 12:19

Community

1
1

answered Jun 04 '10 at 05:43

score 0 · Answer 4 · answered Oct 31 '14 at 21:42

I think you have time attached to the date. If yes, try to convert or cast it. OR Possibly you are storing date in a Varchar Column.

Between clause will return TRUE if the records are FROM the start date and the END date Which mean it includes the START and END date.

score 0 · Answer 5 · answered Jun 04 '10 at 14:11

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

What is wrong with this sql where-clause?

5 Answers5