I have an sql 2005 database with several fields that need to be timezone agnostic. Using nHibernate interceptors we have fixed the application side and everything is happy. The problem is with our reports.
We are using SQL Reporting Services 2005, the reports reside on a server that lives in MST. The users (most, but not all of them) reside in CST. The times are stored in the DB in UTC.
So I am aware of using the following code:
System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!ShipDate.Value) but this will only convert to server time.
The basic question: Is there a way to convert dates in the report either from UTC to the client's time or from MST to the client's time? Ideally the solution would be something akin to the nHibernate Interceptors so I could convert the report dates from UTC on the client.