I’ve built a report within SQL Services Reporting Services 2005 (SSRS) that pulls its data based on a date range, chosen through start and end date parameters.
The problem is, if on those parameters I choose April 21st, it’s actually choosing April 21: 00:00. This means if there’s any data that is relevant during the day on April 21st, it wouldn’t be selected.
I needed a way to have my user select April 21st, but have it return values for end of day April 21st. The solution actually proved easier than I originally thought, as I was making it too complicated.
Full credit to Samson Loo who posted about this (outside of SSRS) here: http://justsamson.com/2010/09/19/date-parameters-and-things-to-consider/
My dataset and linked functions all reference the parameter @EndDate, so I didn’t want to go through and change all of that.
Instead, I created a new report parameter called @EndDateEOD, and set its properties to what @EndDate originally was.
I made sure to place this new parameter above my existing @EndDate.
Then I modified @EndDate to make it hidden, and set this as the default value:
This is adding one day to what the user selects. At first glance this seems like it would produce incorrect information, however in reality, if a user selects April 21st (in reality April 21 00:00:00), then this will use the date April 22 00:00:00 in the dataset, which is what we want.