What is wrong with this sql where-clause?

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 b开发者_C百科e able to select all the records on that date. Any suggestions?

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.

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

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

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

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.