UDF_GetGMTOffset

SELECT GETDATE() AS 'Local Time',
 GETUTCDATE() AS 'GMT Time',
 DATEADD(HOUR, dbo.UDF_GetGMTOffset(), GETUTCDATE()) AS 'GMT Time to Local Time',
 DATEADD(HOUR, dbo.UDF_GetGMTOffset() * -1, GETDATE()) AS 'Local Time to GMT Time',
 dbo.UDF_GetGMTOffset() AS 'GMT Offset'

/*
============================================================================================
            Name: UDF_GetGMTOffset
Parameter(s): (none)
       Returns: (value) = The GMT offset of the SQL Server's local time.
 Description: Calculates the offset between the UTC time and the SQL Server's local time.

                     Note: This function accounts for Daylight Savings Time only if configured
                    correctly in the Windows date and time settings of the SQL Server.
============================================================================================
*/CREATE FUNCTION [dbo].[UDF_GetGMTOffset]()
RETURNS int
AS
BEGIN
    DECLARE @GMTOffset int;
    -- Calculate the difference between the UTC time and the SQL Server's time
    SET @GMTOffset = (SELECT DATEDIFF(hh,GETUTCDATE(),GETDATE()));
    RETURN @GMTOffset;
END
GO