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 »

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 »

It also seems the ConvertToUtc function is also not working.
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: dbo.ConvertTimeFromUtc not working

Post by Lisa Ennis »

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
JDavis
Posts: 97
Joined: Mon Sep 22, 2008 5:10 pm

Re: dbo.ConvertTimeFromUtc not working

Post by JDavis »

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: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: dbo.ConvertTimeFromUtc not working

Post by BobRichards »

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 »

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: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: dbo.ConvertTimeFromUtc not working

Post by BobRichards »

Fantastic! Glad it is working now and thanks for sharing your fix.
Bob Richards, Senior Software Developer, SoftPro
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: dbo.ConvertTimeFromUtc not working

Post by Lisa Ennis »

Yes, thank you for posting an update. Very much appreciated.
Lisa Ennis
Senior Report Developer
SoftPro
brv19067
Posts: 1
Joined: Thu Jun 09, 2016 3:28 pm

Re: dbo.ConvertTimeFromUtc not working

Post by brv19067 »

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: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: dbo.ConvertTimeFromUtc not working

Post by Lisa Ennis »

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
Post Reply