dbo.ConvertTimeFromUtc not working

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
JDavis
Posts: 97
Joined: Mon Sep 22, 2008 5:10 pm

dbo.ConvertTimeFromUtc not working

Post by JDavis » Tue Apr 19, 2016 9:42 pm

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)

JDavis
Posts: 97
Joined: Mon Sep 22, 2008 5:10 pm

Re: dbo.ConvertTimeFromUtc not working

Post by JDavis » Tue Apr 19, 2016 9:50 pm

It also seems the ConvertToUtc function is also not working.

Lisa Ennis
Posts: 81
Joined: Thu Sep 11, 2008 1:57 pm

Re: dbo.ConvertTimeFromUtc not working

Post by Lisa Ennis » Wed Apr 20, 2016 9:03 am

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
Report Developer
SoftPro

JDavis
Posts: 97
Joined: Mon Sep 22, 2008 5:10 pm

Re: dbo.ConvertTimeFromUtc not working

Post by JDavis » Wed Apr 20, 2016 12:04 pm

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.

BobRichards
Posts: 1116
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: dbo.ConvertTimeFromUtc not working

Post by BobRichards » Thu Apr 21, 2016 10:22 am

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

JDavis
Posts: 97
Joined: Mon Sep 22, 2008 5:10 pm

Re: dbo.ConvertTimeFromUtc not working

Post by JDavis » Thu Apr 21, 2016 1:16 pm

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.

BobRichards
Posts: 1116
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: dbo.ConvertTimeFromUtc not working

Post by BobRichards » Fri Apr 22, 2016 10:06 am

Fantastic! Glad it is working now and thanks for sharing your fix.
Bob Richards, Senior Software Developer, SoftPro

Lisa Ennis
Posts: 81
Joined: Thu Sep 11, 2008 1:57 pm

Re: dbo.ConvertTimeFromUtc not working

Post by Lisa Ennis » Fri Apr 22, 2016 10:19 am

Yes, thank you for posting an update. Very much appreciated.
Lisa Ennis
Report Developer
SoftPro

brv19067
Posts: 1
Joined: Thu Jun 09, 2016 3:28 pm

Re: dbo.ConvertTimeFromUtc not working

Post by brv19067 » Tue Oct 25, 2016 4:55 pm

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?

Lisa Ennis
Posts: 81
Joined: Thu Sep 11, 2008 1:57 pm

Re: dbo.ConvertTimeFromUtc not working

Post by Lisa Ennis » Wed Oct 26, 2016 2:29 pm

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
Report Developer
SoftPro

Post Reply