Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UDF_GetGMTOffset Expand / Collapse
Author
Message
Posted Friday, November 26, 2010 1:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:58 AM
Points: 191, Visits: 224
Comments posted to this topic are about the item UDF_GetGMTOffset
Post #1026667
Posted Friday, November 26, 2010 4:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 3:18 AM
Points: 146, Visits: 97
I see that you've defined @GMTOffset as an integer variable.

What happens if you're in Adelaide? Or Darwin? Or Caracas?
Post #1026734
Posted Friday, November 26, 2010 8:46 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:58 AM
Points: 191, Visits: 224
steve.casey (11/26/2010)
I see that you've defined @GMTOffset as an integer variable.

What happens if you're in Adelaide? Or Darwin? Or Caracas?

Better use a TRY/CATCH block!

That's an excellent point - I didn't even considered that when I hastily wrote the script for a project that is only used in the USA.

Although, you could easily modify it to return a real or float if there's a possibility that your deployment might include a time zone that would return an offset requiring additional precision (e.g. Newfoundland, Canada in North America).
Post #1026922
Posted Sunday, November 28, 2010 6:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 3:18 AM
Points: 146, Visits: 97

Better, I think, to stick to returning an offset that is an integer, but one that is in minutes rather than hours.

As I understand it, that's the real way that the international system works. This is a subject that I've had to learn entirely too much about...

And yes, apart from the places I listed, Newfoundland is the only other example that I can find!

Although I do vaguely recall some strange examples in those US areas based around Native American reservations (but it's 1 AM here, I don't have access to my notes, the differences are probably related to Daylight Saving rules anyway and I'm too tired to do any research!).

Cheers, Steve
Post #1027108
Posted Tuesday, December 28, 2010 11:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:58 AM
Points: 191, Visits: 224
steve.casey (11/28/2010)

Better, I think, to stick to returning an offset that is an integer, but one that is in minutes rather than hours.

Good point.

Here's the modified version (finally got around to this ) that simply changes the datepart of the DATEDIFF to "MINUTE" instead of "HOUR".
SET @GMTOffset = (SELECT DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()));

And then you can easily convert the return value from minutes back to hours, but with the proper precision and scale:
SELECT dbo.udf_GetGMTOffset() AS 'GMT Offset Minutes'
,CAST(CAST(dbo.udf_GetGMTOffset() AS numeric) / 60 AS numeric(4,2)) AS 'GMT Offset Hours'

I hope this function makes working with time zone offsets easier for others.

Thanks again Steve for all of your feedback - it's much appreciated!
Post #1039909
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse