Hi there,

i have created an report which retrieve data from table which contains a datetime field.  The data of that particular field is stored in UTC time. when i navigating the report from sharepoint site i wish the datetime field will be converted into local time based on the selected timezone of the user.

i feel this should be a common behaviour but i couldn't  figure out how to achieve that. Can anyone help me on this?

Regards,

SL

Hi SL,

Try this expression

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

Or you can also handle at sql level

DECLARE @yourDateTime DATETIME = '2015-07-29 00:00:00.000'
SELECT DATEADD(MINUTE,-DATEDIFF(MINUTE,GETDATE(),GETUTCDATE()), @yourDateTime)

Hi Milan Das,

Thank you so much for your reply! My report is in remote processing mode.  if i am using the expression, i afraid that the System.TimeZone.CurrentTimeZone will return me the server system timezone always.

I think the  second approach will return the same result as SQL query is excuting in server site also.

what i wish to achieve is conversion based on sharepoint user's setting.

For example for a UTC date '2015-07-29 00:00:00.000' 

if an user set his timezone to  Kuala Lumpur,Singapore , the display date on the report should be '2015-07-29 08:00:00.000

for another user who set his timezone to  Seoul, the display date on the report should be '2015-07-29 09:00:00.000

regards,

SL

Hi SL,

According to your description, you want the report display the date based on the local timezone, right?

In Reporting Services, we can use Now() function to display current date in the report, and the date is based on the report server timezone. In your scenario, if you want to display the date based on the selected timezone, you can use custom code to achieve your goal. For more detail information, please refer to this thread: Working with Time Zones in SSRS. 

In our local environment, we have test the above scenario and the report works properly. 

Reference: 

TimeZoneInfo.ConvertTimeBySystemTimeZoneId Method

If you have any other question, please feel free to ask. 

Regards,
Shrek Li

If you have any feedback on our support, please click here. 

• Edited by Shrek LiMicrosoft contingent staff, Moderator 21 hours 25 minutes ago

Hi Shrek,

I have a quick question,  suppose i have a rdl file, and i am having custom code like this, TimeZoneInfo.ConvertTimeBySystemTimeZoneId(currentTime, UTC, TimeZoneInfo.Local.Id ));

Will the result of all users be the same as the processing is at the server side? based on my understanding it should be. i couldn't test it out as i am in an all in one machine.

thanks for your help!

Hi Lslly,

The time settings will be inherited from the system where the Report server is installed.

Hi SL,

You need to pass the timezone to "TimeZoneInfo.ConvertTimeBySystemTimeZoneId" function. The result is like below:

The expression is like this: =Code.FromUTC(Now(),"Pacific Standard Time").

In your scenario, you can use a report parameter to pass the selected timezone value to the function.

Regards,
Shrek Li