Geek asks and answers

SQL Server: convert between UTC and local time exactly

I have several columns in a SQL Server 2008 R2 database that I need to convert from local time (the time zone in which the sql server is located) to UTC.

I saw quite a few similar questions in StackOverflow, but the answers do not work correctly with daylight saving time, they take into account the current difference and shift the date.

+5
source share
2 answers

I could not find any way to do this using only T-SQL. I solved this with the SQL CLR:

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

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

And the following registration script:

CREATE FUNCTION ToLocalTime(@dateTime DATETIME2) RETURNS DATETIME2 AS EXTERNAL NAME AssemblyName.[AssemblyName.DateTimeFunctions].ToLocalTime; 
GO
CREATE FUNCTION ToUniversalTime(@dateTime DATETIME2) RETURNS DATETIME2 AS EXTERNAL NAME AssemblyName.[AssemblyName.DateTimeFunctions].ToUniversalTime;

It is a shame that you need to switch to such an effort to convert it to and from UTC.

, . , , .

+7
    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 var

    SELECT 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:', @realUTC AS 'UTC', @realLocal AS 'Local'
    UNION
    SELECT 'CONVERTED:', @convertedUTC AS 'UTC', @convertedLocal AS 'Local'
+1

More articles:


All Articles