UDF_GetGMTOffset

Question

Post reply

UDF_GetGMTOffset

~Alex

Ten Centuries

Points: 1119
More actions
November 26, 2010 at 1:01 am

#227200

Comments posted to this topic are about the item UDF_GetGMTOffset

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steve.casey Old Hand Points: 311 More actions · Answer 1

I see that you've defined @GMTOffset as an integer variable.

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

~Alex Ten Centuries Points: 1119 More actions · Answer 2

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).

steve.casey Old Hand Points: 311 More actions · Answer 3

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 Ten Centuries Points: 1119 More actions · Answer 4

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 :rolleyes:) 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! 🙂

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 18, 2016 at 7:00 am

#1879835

Thanks for the script.