SSRS – Get date parameter as end of day

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.

Parameter for EndDateEOD

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.

9 thoughts to “SSRS – Get date parameter as end of day”

  1. 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)))))

  2. 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?

    1. 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.

      1. 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.

        1. 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.

          1. 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.

          2. 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

  3. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.