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:
=DateAdd("d",1,Parameters!EndDateEOD.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.
I took a different approach. I embedded the end of day into the SQL behind the Dataset, as seen here.
where par.RequestDate between @RequestStartDate and dateadd (second,59, (dateadd (minute,59, (dateadd (hour,23,@RequestEndDate)))))
JENIQ, clever method!
Unfortunately, this gets completely confused if the user enters a time value into the End Date parameter…. and I cannot find out a way to distinguish between the user entering midnight and the user not entering a time value.
Any thoughts?
Hi Matthew,
You could modify what is passed to your actual @EndDate parameter value with something like this:
CDate(FormatDateTime(Parameters!EndDateEOD.Value,DateFormat.ShortDate))
As a more complete example, the full value of your @EndDate would be:
=DateAdd(“d”,1,CDate(FormatDateTime(Parameters!EndDateEOD.Value,DateFormat.ShortDate)))
This converts what the user selected to a ShortDate, and then adds 1 day to it. Note, I haven’t specifically tested this. I’m a little surprised your users are entering time values, because the SSRS date/time parameter doesn’t give an interface for time entry.
Hi Jeff,
Although there is no UI for time entry, SSRS correctly processes the time part of a date entered (e.g. “12/12/2016 10:45”) and that is exactly what our users want to be able to do. The problem is that I cant differentiate between “12/12/2016” and “12/12/2016 00:00” which, when used as an end-date, mean totally different things!
If I add a day onto the end-date then it goes wrong if they have entered a time part.
What is it you want to actually pass as a final value to your SQL query?
I just ran a quick test, using the original contents of my post (not my comment from yesterday), and if I enter “12/12/2016 10:45” in the EndDateEOD parameter, then the EndDate parameter outputs 12/13/2016 00:00.
From the database perspective, this works if you’re trying to say “show me everything where DateField is less than EndDate”, because it will capture any time value entered for 12/12/2016, up to 12/12/2016 23:59.
If the user enters an and date of “12/12/2016 10:45” then we pass this exactly as is to the SQL and return all records created up to 10:45 on that date. Users want to be specific about the time, and it works. In other words, we want to return a record created at “12/12/2016 10:44”, but not one created “12/12/2016 10:46”.
This works just as we need, but it all gets more complex when users enter dates without times (which they also want to do), because we have to add 1 day onto the end date (as your original posting) … but (obviously) only if there is no time part entered into the parameter.
This is where the problem arises because when we test whether there is a time part we cannot distinguish a user having entered “12/12/2016” and a user having entered “12/12/2016 00:00”, which are very different things.
There might not be a solution to this, unless we can interrogate (in an expression) the ‘text’ of the parameter rather than the date value (which will have already lost the distinction). The only alternative I can come up with is having “end time” as a separate parameter, which is a bit naff.
Hm, that’s an interesting problem. I think the closest you’ll be able to get is if you set the Default Value on EndDate to this:
=IIf(Hour(Parameters!EndDateEOD.Value) = 0 and Minute(Parameters!EndDateEOD.Value) = 0
, DateAdd(“d”,1,Parameters!EndDateEOD.Value)
, Parameters!EndDateEOD.Value)
If the Time value is 00:00, then it will jump forward one day, otherwise it will use the entered value by the user. This still leaves the problem of someone intending to see 00:00 of their selected date, rather than the “end of day” of the selected date; that might just be a user education issue.
Here’s an RDL file that I used for testing the expression, so you can test it if you want: https://faultbucket.ca/wp-content/uploads/2017/02/Report1.zip
Hi Jeff,
Yes – that’s pretty much the same as what I’m doing at the moment!
Glad to see we’re of the same mind, at least!
Cheers,
Matthgew