dbo.ConvertTimeFromUtc not working
Moderator: Lisa Ennis
dbo.ConvertTimeFromUtc not working
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)
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)
Re: dbo.ConvertTimeFromUtc not working
It also seems the ConvertToUtc function is also not working.
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: dbo.ConvertTimeFromUtc 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.
Lisa Ennis
Senior Report Developer
SoftPro
Senior Report Developer
SoftPro
Re: dbo.ConvertTimeFromUtc not working
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.
-
- Posts: 1382
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: dbo.ConvertTimeFromUtc not working
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.
Bob Richards, Senior Software Developer, SoftPro
Re: dbo.ConvertTimeFromUtc not working
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.
-
- Posts: 1382
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: dbo.ConvertTimeFromUtc not working
Fantastic! Glad it is working now and thanks for sharing your fix.
Bob Richards, Senior Software Developer, SoftPro
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: dbo.ConvertTimeFromUtc not working
Yes, thank you for posting an update. Very much appreciated.
Lisa Ennis
Senior Report Developer
SoftPro
Senior Report Developer
SoftPro
Re: dbo.ConvertTimeFromUtc not working
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?
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?
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: dbo.ConvertTimeFromUtc not working
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.
Lisa Ennis
Senior Report Developer
SoftPro
Senior Report Developer
SoftPro