Conditionally display end date if different to start date

Conditionally display end date if different to start date

With SQL Server Reporting Services (SSRS) and the power of AD HOC reporting, we’ll offer a StartDate and EndDate picker for many, if not all the reports.

Many reports though are Daily reports, causing the user to have to pick yesterday as the StartDate and yesterday as the EndDate.

This is fine, but if the report displays the parameters to notify the user the date range selected, you end up with something silly, like:

Period: 6/5/2014 5:04:03 AM to 6/5/2014 5:04:03 AM

This expression will clean that up, detecting if the start and end dates are the same (and displaying one) as well as formatting the output to a more friendly:

Period: Thu 5th Jun 2014

If a greater range is selected (ie. Start and End dates differ) the display is:

Period: Thu 5th Jun 2014 to Fri 6th Jun 2014

=iif(Parameters!StartDate.Value = Parameters!EndDate.Value,
"Period: " & Format(Parameters!StartDate.Value, "ddd dd MMM yyyy"),
"Period: " & Format(Parameters!StartDate.Value, "ddd dd MMM yyyy") & 
" to " & Format(Parameters!EndDate.Value, "ddd dd MMM yyyy"))

For more information on formatting datetime in SSRS, click here. I hope this helps!


Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
Leave a comment

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