datetime - Working with Time Zones in SSRS

Question

We store all our dates SQL Server 2008 database in UTC time in DateTime columns. I'm using SSRS to create reports and I need to convert all the times on the reports to the Time Zone of the computer where they're running the report from.

I know could always just pass in the current timezone offset as a parameter to the report and add or subtract the offset from the timezone, but this wouldn't correctly show historical dates because of daylight savings.

Does SSRS have any functions that handle this? Should I pass the timezone to the SQL server functions and have the SQL Server convert the time?

Answer 1

I figured it out. In the SSRS report, I've added a reference to the assembly System.Core

Then anywhere I needed to convert the timezone I used:

=Code.FromUTC(Fields!UTCDateFromDatabase.Value, Parameters!TimeZone.Value)

where Parameters!TimeZone.Value is the string value of the timezone which can be retrieved in the application by using TimeZone.CurrentTimeZone.StandardName

I should probably put what FromUTC does:

Shared Function FromUTC(ByVal d As Date, ByVal tz As String) As Date
 Return (TimeZoneInfo.ConvertTimeBySystemTimeZoneId(d, TimeZoneInfo.Utc.Id, tz))
end function