SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UDF_GetGMTOffset


UDF_GetGMTOffset

Author
Message
~Alex
~Alex
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 224
Comments posted to this topic are about the item UDF_GetGMTOffset
steve.casey
steve.casey
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 112
I see that you've defined @GMTOffset as an integer variable.

What happens if you're in Adelaide? Or Darwin? Or Caracas?
~Alex
~Alex
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 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! :-P

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).
steve.casey
steve.casey
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 112
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
~Alex
~Alex
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 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 :rolleyesSmile 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! :-)
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14130 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search