Date Filtering SSRS report issue

Today, one of our users reported an issue with one of our SSRS reports. The report in question allows the user to filter a report between two dates using dynamic filtering allowing them to choose which date field to filter on.

The user was reporting that some data was missing the upper boundary i.e. running for 30/03/2018 to 31/03/2018 was missing data from the 31st.

After checking the  query in the SSRS data set to ensure the correct "greater than" and "less than" logic I noticed it was using the SSRS between operator however, everything appeared to be setup fine.

This told me the offending item must be the time portion of the time stamp. The fields we are filtering on in CRM are all setup as date only fields which  means CRM stores the date as "00:00:00" therefore should not cause a problem in this case.

However, we recently imported data from a legacy system and some of the records were imported with a timestamp other than midnight.

I now understood the problem.

The SSRS between function is looking for records between 30/03/2018 00:00:00 and 31/03/2018 00:00:00 and is not including anything from the 31/03/2018 which was edited after midnight.


To fix I created a calculated field for each date filter which converted the date to a formatted string which essentially removes the time from it. I then used the CDATE function to convert it back to a datetime field which will set the time to 00:00:00. I then used these fields in my filter expression and everything works as normal .

It feels like there probably is a better way to deal with this  and I am happy to hear from anyone else in the community on better ways but the above solution worked for me and allowed the user to quickly begin using the report again.

• Twitter
• LinkedIn