Date parameter in Microsoft Report Builder.

Question

Good Afternoon,

I have written an SQL query that returns specific door access activity on any given date, and then breaks it down in to In and Out per hour of that date.

If I use
WHERE eventtime = '02/02/2020'
I would get no results, so I have to use WHERE eventtime BETWEEN '02/02/2020' AND '02/03/2020'
to get the results I want within that 24 hour period, which is fine, no issue there.

The issue I have is with turning this in to a report in Microsoft Report Builder without having to use two parameters Start date and End Date.
I just want to use the date parameter to select the Start Date and have the report builder automatically turn that in to the 24 hour period that I need to display the specific records.

I hope that makes sense.

Kind Regards

Greg

Answer 1

Hello Greg,

Is the data source a MS SQL Server? Then you can use the SQL function DATEADD to add a day on the parameter value

SELECT *  
FROM yourTable  
WHERE eventtime BETWEEN '02/02/2020' AND DATEADD(day, 1, '02/02/2020')  

Answer 2

Olaf's answer is not entirely correct. The WHERE clause should be written litke this:

WHERE eventtime >= '02/02/2020' 
   AND eventtime < DATEADD(day, 1, '02/02/2020')

You should probably not include events that fall exactly on midnight the next date.

Answer 3

Adding the above syntax at the END of the overall query does not work. WHERE in the query should this be placed? Can someone please provide an example video, or an example with screen shots? Many thanks!