SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Alexander MacGregor / UDF_GetGMTOffset / Latest Posts

RE: UDF_GetGMTOffset

~Alex — Tue, 28 Dec 2010 11:32:33 GMT

[quote][b]steve.casey (11/28/2010)[/b][hr]Better, I think, to stick to returning an offset that is an integer, but one that is in minutes rather than hours.[/quote]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".[code="sql"]SET @GMTOffset = (SELECT DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()));[/code]And then you can easily convert the return value from minutes back to hours, but with the proper precision and scale:[code="sql"]SELECT dbo.udf_GetGMTOffset() AS 'GMT Offset Minutes' ,CAST(CAST(dbo.udf_GetGMTOffset() AS numeric) / 60 AS numeric(4,2)) AS 'GMT Offset Hours'[/code]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! :-)

RE: UDF_GetGMTOffset

steve.casey — Sun, 28 Nov 2010 18:13:20 GMT

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

RE: UDF_GetGMTOffset

~Alex — Fri, 26 Nov 2010 20:46:38 GMT

[quote][b]steve.casey (11/26/2010)[/b][hr]I see that you've defined @GMTOffset as an [b]integer[/b] variable.What happens if you're in Adelaide? Or Darwin? Or Caracas?[/quote]Better use a TRY/CATCH block! :-PThat'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).

RE: UDF_GetGMTOffset

steve.casey — Fri, 26 Nov 2010 04:45:41 GMT

I see that you've defined @GMTOffset as an [b]integer[/b] variable.What happens if you're in Adelaide? Or Darwin? Or Caracas?

UDF_GetGMTOffset

~Alex — Fri, 26 Nov 2010 01:01:45 GMT

Comments posted to this topic are about the item [B]UDF_GetGMTOffset[/B]