SoftPro Developer Forum

In our report SPROCs we use the ConvertTimeFromUtc function to convert to local time. At some point in time, this stopped working in our production environment. When running the below query, the date is incorrectly returned as the UTC date that was passed in. When running this on the our staging or development environments it returns the correctly converted date. When running Select, the dates seem to be displaying correctly as the converted local time.

select dbo.ConvertTimeFromUtc('2016-04-20 00:08:04.663', 'Hawaiian Standard Time')

2016-04-20 00:08:04.663 - production value returned
2016-04-19 14:08:04.000 - staging and dev value returned (correct)

It also seems the ConvertToUtc function is also not working.

I copied your select query above and it returns '2016-04-19 14:08:04.000' for me. Is it failing in every query, or just specific ones? Double check how your time zone is typed in the query as I believe it is case sensitive.

Simply running the SQL statement above returns the incorrect value in our production database in SQL Management Studio. Running in our staging and development databases return the correctly converted values.

This issue sounds like a setup/environmental issue that is beyond the scope of these forums. Please discuss this with your SoftPro customer support contact.

We were able to fix the problem by restarting the SQL server. I'm assuming this is something with the CLR object that the functions use. I first made a backup of the affected database and restored it to the same server - the function worked fine in this copied database.

Fantastic! Glad it is working now and thanks for sharing your fix.

Yes, thank you for posting an update. Very much appreciated.

We are experiencing the same issue as before on our Production servers where the ConvertTimeFromUtc function to convert to local time variables, is not working. When running the below query, the date is incorrectly returned as the UTC date that was passed in. When running this on the our staging or development environments it returns the correctly converted date. When running Select, the dates seem to be displaying correctly as the converted local time.

select dbo.ConvertTimeFromUtc('2016-04-20 00:08:04.663', 'Hawaiian Standard Time')

2016-04-20 00:08:04.663 - production value returned
2016-04-19 14:08:04.000 - staging and dev value returned (correct)

Do you happen to know of any resolution to this issue?

In your previous post, you had noted that restarting your server corrected the issue. This is likely a setup/environmental issue that is beyond the scope of these forums. Please discuss this with your SoftPro customer support contact.