CODEWITHSUNDEEP
sqlsql-serverdatereporting-servicesparameters
sql2015
sql2015

Reputation: 611

ssrs date param

I have a SQL query for an SSRS report to return results where record was created between 2 dates, I am using parameters as for the date values

select * from results
where [startdate] >=@datestart or [startdate] <=@dateend

when I run the SSRS report and select the same date for @startdate and @enddate I get 2 records returned which I know is incorrect. when I run the SQL query and use a date eg '01 feb 2019' rather than the parameter I get different results. Do I need to exclude time from my parameters or set the time for @startdate to be 00:00:00 and set @dateend to be 23:59:59 to get the same results if I was using an actual date?

Upvotes: 0

Views: 952

Answers (3)

iamdave
iamdave

Reputation: 12243

If your SSRS parameters are date values and your data is datetime values the easiest solution is to add one day to your @dateend and then look for any datetime value before but not equal to it.

Using a < instead of a <= is important because time is infinite. In your example of changing the @enddate value to have 23:59:59 appended, any datetime values between 23:59:59 and the end of the day (such as 23:59:59.5) will not be included.

As such, you could structure you script like this:

select *
from results
where [startdate] >= @datestart
    and [startdate] < dateadd(day,1,@dateend)

Upvotes: 1

shreyamsh
shreyamsh

Reputation: 43

@imadave's solution will work perfectly.

Also, you could just use the "BETWEEN" clause to get the result. It will return all dates between the given range (inclusive). When the time part is unspecified, it defaults to 12:00 A.M.

SELECT * FROM results 
WHERE [startdate] BETWEEN @datestart AND @dateend

Upvotes: 0

Anand Tiwari
Anand Tiwari

Reputation: 1

You should truncate datetime to date only.

select * from results
  where cast(startdate As Date) >= @datestart
    and cast(startdate As Date) <= @dateend

Upvotes: 0

Related Questions