Sql Server: Convert Between Utc And Local Time Precisely

I have a few columns in an SQL server 2008 R2 database that i need to convert from local time (the time zone the sql server is in) to UTC. I have seen quite a few similar questions

Solution 1:

I could not find any way at all doing this using T-SQL alone. I solved it using SQL CLR:

public static classDateTimeFunctions
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static DateTime? ToLocalTime(DateTime? dateTime)
    {
        if (dateTime == null) returnnull;
        return dateTime.Value.ToLocalTime();
    }

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static DateTime? ToUniversalTime(DateTime? dateTime)
    {
        if (dateTime == null) returnnull;
        return dateTime.Value.ToUniversalTime();
    }
}

And the following registration script:

CREATEFUNCTIONToLocalTime(@dateTime DATETIME2) RETURNSDATETIME2ASEXTERNALNAMEAssemblyName.[AssemblyName.DateTimeFunctions].ToLocalTime; 
GOCREATEFUNCTIONToUniversalTime(@dateTime DATETIME2) RETURNSDATETIME2ASEXTERNALNAMEAssemblyName.[AssemblyName.DateTimeFunctions].ToUniversalTime; 

It is a shame to be forced to go to such effort to convert to and from UTC time.

Note, that these functions interpret local time as whatever is local to the server. It is recommended to have clients and servers set to the same time zone to avoid any confusion.

Solution 2:

DECLARE@convertedUTC datetime, @convertedLocal datetime
    SELECT DATEADD(
                    HOUR,                                    -- Add a number of hours equal to
                    DateDiff(HOUR, GETDATE(), GETUTCDATE()), -- the difference of UTC-MyTime
                    GetDate()                                -- to MyTime
                    )

    SELECT@convertedUTC= DATEADD(HOUR,DateDiff(HOUR, GETDATE(), GETUTCDATE()),GetDate()) --Assign the above to a varSELECT DATEADD(
                    HOUR,                                    -- Add a number of hours equal to
                    DateDiff(HOUR, GETUTCDATE(),GETDATE()), -- the difference of MyTime-UTC@convertedUTC-- to MyTime
                    )

    SELECT@convertedLocal= DATEADD(HOUR,DateDiff(HOUR, GETUTCDATE(),GETDATE()),GetDate()) --Assign the above to a var/* Do our converted dates match the real dates? */DECLARE@realUTC datetime = (SELECT GETUTCDATE())
    DECLARE@realLocal datetime = (SELECT GetDate())

    SELECT'REAL:', @realUTCAS'UTC', @realLocalAS'Local'UNIONSELECT'CONVERTED:', @convertedUTCAS'UTC', @convertedLocalAS'Local'