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! 🙂