Date range

What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @.Begin Date AND @.EndDate. I want to include all of the @.EndDate.well, BETWEEN is inclusive but your probably not receiving all of the enddates due to the time part of datetime datatype.

Another method is to add 1 day to the enddate (with no time indicator) and use:

WHERE date1 >= @.beginDate AND date1 < @.enddate

|||I used WHERE date1 BETWEEN @.Begin Date AND @.EndDate + '24:59:59' to include the data from the last day of the search.

So I assume there is no benefit from using between to < and >.|||Your query is not correct. It will exclude 1 second before midnight (SQL Server datetimes do include ms)

You should be using:

where Date1 >= @.Begin
 and Date1 < @.End + 1

OR

where convert(datetime, convert(varchar(10), Date1, 111), 111) = @.Begin

if it's only one day|||Using the CONVERT function would make the query non-sargable -- don't do it that way!! Definitely stick with Pierre's first option.

Terri|||Adding one to the date doesn't pose any issues? @.date + 1?|||As long as @.date does not contain any time data (ie it is the default midnight 00:00:00.000) that method should be fine.

Terri