How to handle UTC dates in a report

Question

I have a report with two parameters - StartDate and EndDate - which run a stored procedure that returns data between these two dates.

The dates in the database are stored in UTC. Currently in the UK we are on BST (UTC+1).

To show the times in the correct time zone, in the report I am using System.TimeZone.CurrentTimeZone.ToLocalTime.

The end result is that the times as displayed on the report look correct, but the user still has to enter the StartDate and EndDate parameters in UTC. This confuses the user greatly as they expect to be able to enter a date in the current time zone, but the times in the database are UTC.

How can I allow the date parameters to be entered in the correct time zone?

Unfortunately, we can't modify the schema of the database so I need to find a way to fix this on the reporting side

Answer 1

In SSRS, if the date stored in database is UTC, it has to be UTC when select from a query in parameter setting. It is not supported to change the field which is from a query in parameter. For your requirement, you can either format the date into local time in query. Or you can select UTC date in parameter, and render it as local time