SQL Server - Convert date field to UTC

Question

I have recently updated my system to record date/times as UTC as previously they were storing as local time.

I now need to convert all the local stored date/times to UTC. I was wondering if there is any built in function, similar to .NET's ConvertTime method?

I am trying to avoid having to write a utility app to do this for me.

Any suggestions?

Answer 1

Here is a tested procedure that upgraded my database from local to utc time. The only input required to upgrade a database is to enter the number of minutes local time is offset from utc time into @Offset and if the timezone is subject to daylight savings adjustments by setting @ApplyDaylightSavings.

For example, US Central Time would enter @Offset=-360 and @ApplyDaylightSavings=1 for 6 hours and yes apply daylight savings adjustment.

Supporting Database Function

---

CREATE FUNCTION [dbo].[GetUtcDateTime](@LocalDateTime DATETIME, @Offset smallint, @ApplyDaylightSavings bit) 
RETURNS DATETIME AS BEGIN 

    --====================================================
    --Calculate the Offset Datetime
    --====================================================
    DECLARE @UtcDateTime AS DATETIME
    SET @UtcDateTime = DATEADD(MINUTE, @Offset * -1, @LocalDateTime)

    IF @ApplyDaylightSavings = 0 RETURN @UtcDateTime;

    --====================================================
    --Calculate the DST Offset for the UDT Datetime
    --====================================================
    DECLARE @Year as SMALLINT
    DECLARE @DSTStartDate AS DATETIME
    DECLARE @DSTEndDate AS DATETIME

    --Get Year
    SET @Year = YEAR(@LocalDateTime)

    --Get First Possible DST StartDay
    IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
    ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
    --Get DST StartDate 
    WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)


    --Get First Possible DST EndDate
    IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'
    ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'

    --Get DST EndDate 
    WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)

    --Finally add the DST Offset if needed 
    RETURN CASE WHEN @LocalDateTime BETWEEN @DSTStartDate AND @DSTEndDate THEN 
        DATEADD(MINUTE, -60, @UtcDateTime) 
    ELSE 
        @UtcDateTime
    END

END
GO

Upgrade Script

---

1. Make a backup before running this script!
2. Set @Offset & @ApplyDaylightSavings
3. Only run once!

---

begin try
    begin transaction;

    declare @sql nvarchar(max), @Offset smallint, @ApplyDaylightSavings bit;

    set @Offset = -360;             --US Central Time, -300 for US Eastern Time, -480 for US West Coast
    set @ApplyDaylightSavings = 1;  --1 for most US time zones except Arizona which doesn't observer daylight savings, 0 for most time zones outside the US

    declare rs cursor for
    select 'update [' + a.TABLE_SCHEMA + '].[' + a.TABLE_NAME + '] set [' + a.COLUMN_NAME + '] = dbo.GetUtcDateTime([' + a.COLUMN_NAME + '], ' + cast(@Offset as nvarchar) + ', ' + cast(@ApplyDaylightSavings as nvarchar) + ') ;'
    from INFORMATION_SCHEMA.COLUMNS a
        inner join INFORMATION_SCHEMA.TABLES b on a.TABLE_SCHEMA = b.TABLE_SCHEMA and a.TABLE_NAME = b.TABLE_NAME
    where a.DATA_TYPE = 'datetime' and b.TABLE_TYPE = 'BASE TABLE' ;

    open rs;
    fetch next from rs into @sql;
    while @@FETCH_STATUS = 0 begin
        exec sp_executesql @sql;
        print @sql;
        fetch next from rs into @sql;
    end
    close rs;
    deallocate rs;

    commit transaction;
end try
begin catch
    close rs;
    deallocate rs;

    declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    rollback transaction;
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

Answer 2

As mentioned here previously, there is no build-in way to perform time zone rules aware date conversion in SQL Server (at least as of SQL Server 2012).

You have essentially three choices to do this right:

1. Perform the conversion outside of SQL Server and store results in the database
2. Introduce time zone offset rules in a standalone table and create stored procedures or UDFs to reference the rules table to perform conversions. You can find one take on this approach over at SQL Server Central (registration required)
3. You can create a SQL CLR UDF; I will describe the approach here

While SQL Server does not offer tools to perform time zone rules aware date conversion, the .NET framework does, and as long as you can use SQL CLR, you can take advantage of that.

In Visual Studio 2012, make sure you have the data tools installed (otherwise, SQL Server project won't show up as an option), and create a new SQL Server project.

Then, add a new SQL CLR C# User Defined Function, call it "ConvertToUtc". VS will generate boiler plate for you that should look something like this:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ConvertToUtc()
    {
        // Put your code here
        return new SqlString (string.Empty);
    }
}

We want to make several changes here. For one, we want to return a SqlDateTime rather than a SqlString. Secondly, we want to do something useful. :)

Your revised code should look like this:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime ConvertToUtc(SqlDateTime sqlLocalDate)
    {
        // convert to UTC and use explicit conversion
        // to return a SqlDateTime
        return TimeZone.CurrentTimeZone.ToUniversalTime(sqlLocalDate.Value);
    }
}

At this point, we are ready to try it out. The simplest way is to use the built-in Publish facility in Visual Studio. Right-click on the database project and select "Publish". Set up your database connection and name, and then either click "Publish" to push the code into the database or click "Generate Script" if you'd like to store the script for posterity (or to push the bits into production).

Once you have the UDF in the database, you can see it in action:

declare @dt as datetime
set @dt = '12/1/2013 1:00 pm'
select dbo.ConvertToUtc(@dt)

Answer 3

If they're all local to you, then here's the offset:

SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime

and you should be able to update all the data using:

UPDATE SomeTable
   SET DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)

Would that work, or am I missing another angle of this problem?

Answer 4

If you have to convert dates other than today to different timezones you have to deal with daylight savings. I wanted a solution that could be done without worrying about database version, without using stored functions and something that could easily be ported to Oracle.

I think Warren is on the right track with getting the correct dates for daylight time, but to make it more useful for multiple time zone and different rules for countries and even the rule that changed in the US between 2006 and 2007, here a variation on the above solution. Notice that this not only has us time zones, but also central Europe. Central Europe follow the last sunday of april and last sunday of october. You will also notice that the US in 2006 follows the old first sunday in april, last sunday in october rule.

This SQL code may look a little ugly, but just copy and paste it into SQL Server and try it. Notice there are 3 section for years, timezones and rules. If you want another year, just add it to the year union. Same for another time zone or rule.

select yr, zone, standard, daylight, rulename, strule, edrule, yrstart, yrend,
    dateadd(day, (stdowref + stweekadd), stmonthref) dstlow,
    dateadd(day, (eddowref + edweekadd), edmonthref)  dsthigh
from (
  select yrs.yr, z.zone, z.standard, z.daylight, z.rulename, r.strule, r.edrule, 
    yrs.yr + '-01-01 00:00:00' yrstart,
    yrs.yr + '-12-31 23:59:59' yrend,
    yrs.yr + r.stdtpart + ' ' + r.cngtime stmonthref,
    yrs.yr + r.eddtpart + ' ' + r.cngtime edmonthref,
    case when r.strule in ('1', '2', '3') then case when datepart(dw, yrs.yr + r.stdtpart) = '1' then 0 else 8 - datepart(dw, yrs.yr + r.stdtpart) end
    else (datepart(dw, yrs.yr + r.stdtpart) - 1) * -1 end stdowref,
    case when r.edrule in ('1', '2', '3') then case when datepart(dw, yrs.yr + r.eddtpart) = '1' then 0 else 8 - datepart(dw, yrs.yr + r.eddtpart) end
    else (datepart(dw, yrs.yr + r.eddtpart) - 1) * -1 end eddowref,
    datename(dw, yrs.yr + r.stdtpart) stdow,
    datename(dw, yrs.yr + r.eddtpart) eddow,
    case when r.strule in ('1', '2', '3') then (7 * CAST(r.strule AS Integer)) - 7 else 0 end stweekadd,
    case when r.edrule in ('1', '2', '3') then (7 * CAST(r.edrule AS Integer)) - 7 else 0 end edweekadd
from (
    select '2005' yr union select '2006' yr -- old us rules
    UNION select '2007' yr UNION select '2008' yr UNION select '2009' yr UNION select '2010' yr UNION select '2011' yr
    UNION select '2012' yr UNION select '2013' yr UNION select '2014' yr UNION select '2015' yr UNION select '2016' yr
    UNION select '2017' yr UNION select '2018' yr UNION select '2018' yr UNION select '2020' yr UNION select '2021' yr
    UNION select '2022' yr UNION select '2023' yr UNION select '2024' yr UNION select '2025' yr UNION select '2026' yr
) yrs
cross join (
    SELECT 'ET' zone, '-05:00' standard, '-04:00' daylight, 'US' rulename
    UNION SELECT 'CT' zone, '-06:00' standard, '-05:00' daylight, 'US' rulename
    UNION SELECT 'MT' zone, '-07:00' standard, '-06:00' daylight, 'US' rulename
    UNION SELECT 'PT' zone, '-08:00' standard, '-07:00' daylight, 'US' rulename
    UNION SELECT 'CET' zone, '+01:00' standard, '+02:00' daylight, 'EU' rulename
) z
join (
    SELECT 'US' rulename, '2' strule, '-03-01' stdtpart, '1' edrule, '-11-01' eddtpart, 2007 firstyr, 2099 lastyr, '02:00:00' cngtime
    UNION SELECT 'US' rulename, '1' strule, '-04-01' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2006 lastyr, '02:00:00' cngtime
    UNION SELECT  'EU' rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime
) r on r.rulename = z.rulename
    and datepart(year, yrs.yr) between firstyr and lastyr
) dstdates

For the rules, use 1, 2, 3 or L for first, second, third or last sunday. The date part gives the month and depending on the rule, the first day of the month or the last day of the month for rule type L.

I put the above query into a view. Now, anytime I want a date with the time zone offset or converted to UTC time, I just join to this view and select get the date in the date format. Instead of datetime, I converted these to datetimeoffset.

select createdon, dst.zone
    , case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end pacificoffsettime
    , TODATETIMEOFFSET(createdon, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end) pacifictime
    , SWITCHOFFSET(TODATETIMEOFFSET(createdon, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end), '+00:00')  utctime
from (select '2014-01-01 12:00:00' createdon union select '2014-06-01 12:00:00' createdon) photos
left join US_DAYLIGHT_DATES dst on createdon between yrstart and yrend and zone = 'PT'

Answer 5

In case anyone stumbles across this and uses Chris Barlow's answer, there's a typo where 2018 is listed twice, and the second time should be 2019. Specifically this line:

   UNION select '2017' yr UNION select '2018' yr UNION select '2018' yr UNION select '2020' yr UNION select '2021' yr

Sorry I don't have enough points to comment on Chris's answer. Other than that, the answer is great!

Answer 6

The following should work as it calculates difference between DATE and UTCDATE for the server you are running and uses that offset to calculate the UTC equivalent of any date you pass to it. In my example, I am trying to convert UTC equivalent for '1-nov-2012 06:00' in Adelaide, Australia where UTC offset is -630 minutes, which when added to any date will result in UTC equivalent of any local date.

select DATEADD(MINUTE, DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()), '1-nov-2012 06:00')