ssrs date param

0
votes

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?

3
sqlsql-serverdatereporting-servicesparameters
i've added SQL server tag because SSRS (SQL Server Reporting Services) was mentioned – Raymond Nijland
That or in your script should really be an and by the way... – iamdave
Yes, you need to either exclude the time portion from the parameter values passed or do that in the T-SQL statement. Also, I think you probably want AND instead of OR here. When the underlying column includes a time, it is best to specify an inclusive start date and exclusive end date, such as WHERE [startdate] >=@datestart AND [startdate] < DATEADD(day, 1, @dateend). – Dan Guzman

3 Answers

1
votes

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)
0
votes

You should truncate datetime to date only.

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

@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