Date range issue

Question

Hi all,
We have a report that has several parameters, two of which are start and end dates. An issue has been detected that if both start and end date are the same date, for example 10/6/2020, data for 10/7/2020 is also returned. The stored procedure uses the BETWEEN function. I even used the CAST function to strip the time part of the date, but that didn't fix the problem. Any idea's what is causing this problem? Is there a way in the stored procedure to check if both dates are the same, to just use date = Start date instead of date BETWEEN start date and end date?

Thank you for your input,

Seyed

Answer 1

Hi Zoe,
Per your suggestion, I tried this again and created a simple report using EMPLOYEES table in my Oracle database. I created the stardate and enddate parameters and made sure datatype for both were datetime and then in the tablix properties pane, I added a filter as you had done, ran the report for the date range and it worked and report listed data for the selected date range.

Thank you so much!

Seyed

Answer 2

Hi @S G ,

I tried to create two report parameters named as @StartDate and @EndDate.

Set its data type to date/time in parameter properties pane. Here, there is no need to specify any available value or default value for these two parameters so that you can directly use calendar to select date time and filter tablix.

Then, add filters in tablix properties pane.

Here is my preview result.
From 2020/03/04 to 2020/03/04.

From 2020/03/04 to 2020/03/05.

If the date are the same date, it will only show the data which you have chosen.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi Zoe,
If that is the case, I really don't need to filter on the date range in the stored procedure. I can get rid of that line in the 'WHERE' clause that contains the CAST function and just filter on the date in the report as you have shown above. I will give that a try and will update this post with my result.

Thank you!

Seyed

Answer 4

Hi Zoe,
I removed the lines in the stored procedure and recompiled the stored procedure without them. While there, I also made sure both start and end parameters were datetime data types. In the Report Builder, I dropped the dataset, added it back. Did an 'Add filter and then selected the 'Transaction' date column. Then selected 'BETWEEN' and entered the the date start and end parameters each beginning with [@START ] and [@Eslam Nader ]. In the end, when I ran this report for '3/6/2017' by selecting them from the calendar, I got no data back.

What have I done wrong?

Thank you very much,

Seyed