SQL Between Two Dates Missing End Date (SSRS)

Question

I'm using SSRS 2008r2 to generate reports. Using following WHERE statement in SQL query

WHERE (NonPMJobs.npmJobCreateDate >= @Created_Parameter) AND     
      (NonPMJobs.npmJobCreateDate <= @Created_Parameter2)

I'm using parameters with the data type of DateTime. Users then select day from a calendar. I want to get results where jobs have been created between date 1 (@Created_Parameter) AND date 2 (@Created_Parameter2) INCLUSIVE.

But results being returned do not include the second date (@Created_Parameter2). If I select 01/07/2013 - 05/07/2013 I get 01, 02, 03, 04 but no 05. If I select 01/07/2013 - 06/07/2013 I get 01, 02, 03, 04, 05.

I've tried using:

WHERE (NonPMJobs.npmJobCreateDate BETWEEN @Created_Parameter AND @Created_Parameter2)

but get same results.

What am I missing here and why isn't WHERE statement inclusive? Any pointers would be very much appreciated.

Answer 1

Well, you need to think about this: a DATETIME like this: 05/07/2013 means the 5th of July (or 7th of May - depending on your locale) at midnight when the day starts (a 00:00 hours, so to speak).

So this does NOT include the events that happen on that day!

The best solution would be to use

WHERE (NonPMJobs.npmJobCreateDate >= @Created_Parameter) AND     
      (NonPMJobs.npmJobCreateDate < DATEADD(DAY, 1, @Created_Parameter2))

and basically getting everything that's smaller than the next day based on @Created_Parameter2. So for your 5th of July, that would be anything before the 6th of July - which includes all events from the 5th of July.