Script of the Day: Find UTC from Current Offset


I don’t remember where I got this function, but I needed it for a distributed system where I needed a standard time unit. I’m sure there are other ways to do this, but this worked out for me:


/* fn_ConvertToUTC.SQL


Convert local time to UTC


Author: Unknown


Date: 08/28/2007


*/


CREATE FUNCTION dbo.fn_ConvertToUTC


(@InDate datetime)


RETURNS datetime


AS


BEGIN


— Get the current time offset:


DECLARE @DeltaGMT int


EXECUTE master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’,


‘SYSTEM\CurrentControlSet\Control\TimeZoneInformation’,


‘ActiveTimeBias’,


@DeltaGMT OUT


— Subtract to go back to the correct time


RETURN (SELECT DATEADD(Minute, (@DeltaGMT), @InDate))


END;


GO


select dbo.fn_ConvertToUTC(getdate())

Comments (1)

  1. rsocol says:

    Here is another way:

    ALTER FUNCTION dbo.fn_ConvertToUTC (@InDate datetime)

    RETURNS datetime AS BEGIN

    DECLARE @DeltaGMT int

    SET @DeltaGMT=ROUND(DATEDIFF(Second, GETDATE(), GETUTCDATE()),-1)/60

    RETURN (SELECT DATEADD(Minute, (@DeltaGMT), @InDate))

    END;

    Razvan Socol

    SQL Server MVP