17

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?

jamiegs
  • 1,761
  • 2
  • 15
  • 23

2 Answers2

14

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
jamiegs
  • 1,761
  • 2
  • 15
  • 23
  • This is fine if you are only calling the function it a few times, but not if you are calling the function for every row. For a large report, calling your function for every row will impact performance of exports. I haven't been able to find an alternative unfortunately. – Kev Jan 13 '14 at 17:52
  • @ClassyFedora - Not specifically SSRS, but [this might help](https://github.com/mj1856/SqlServerTimeZoneSupport) – Matt Johnson-Pint Apr 03 '15 at 05:32
  • Does anybody have a new way of doing this? The code required for this in the TimeZoneInfo dll is unsecured so does not work in sql server 2017. We are being forced to find a workaround – Stew Apr 15 '19 at 14:59
10

Try using this instead: 

=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

For SSRS reports called from AX 2012, this is what I use.

Credit goes to this post: http://social.msdn.microsoft.com/Forums/en-US/500448a3-bf58-44ab-8572-81becd67d8b8/convert-utc-time-to-local-time?forum=sqlreportingservices

rjv
  • 1,058
  • 11
  • 29
  • 6
    Does this really work? The reports are rendered on server side. This would convert to local server time only, not to the local time of the user. – m0n0ph0n May 13 '15 at 02:15
  • It does! It will actually use the same time as the session time that is setup in Dynamics 365 for Finance and Operations, even if that is different than the local system time. However, I have not use it recently (many years) in AX 2012, so I'm not sure if that was correct for AX 2012 when I originally posted :) – rjv Jul 30 '18 at 11:58