SQL Server - convert date field to UTC

Question

I recently updated my system to record date / time in UTC, as previously they were saved as local time.

Now I need to convert all local stored date / time to UTC. I was wondering if there is a built-in function similar to the .NET ConvertTime method?

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

Any suggestions?

Answer 1

If they are all local to you, then here is the offset:

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

and you can update all the data using:

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

Will this work, or am I missing another corner of this problem?

Answer 2

I do not believe the above code will work. The reason is that it depends on the difference between the current date in local and UTC times. For example, here in California, we are now in PDT (Pacific Daylight Time); the difference between this time and UTC is 7 hours. The code provided, if executed now, will add 7 hours to each date that needs to be converted. But if the historical stored date or future date is converted and this date is not executed during daylight saving time, it will still add 7 when the correct offset is 8. Bottom line: you cannot correctly convert the date / time between time zones (including UTC that does not observe daylight saving time), just looking at the current date. You must consider the date you are converting yourself as to whether daylight saving time was valid on that date. In addition, the dates in which daylight and standard times have changed have changed (George W. Bush changed dates during his administration for the USA!). In other words, any solution that even refers to getdate () or getutcdate () does not work. He must analyze the actual date to be converted.

Answer 3

SQL Server 2016 now has built-in time zone support using the AT TIME ZONE statement. You can link them to make conversions:

 SELECT YourOriginalDateTime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC' 

Or it will work too:

 SELECT SWITCHOFFSET(YourOriginalDateTime AT TIME ZONE 'Pacific Standard Time', '+00:00') 

Any of them will interpret the input in Pacific Time, correctly consider whether the DST is valid, and then converted to UTC. The result will be a datetimeoffset with a zero offset.

Additional examples in the CTP declaration.

Answer 4

As mentioned earlier, there is no built-in way to perform date conversion in timezone mode in SQL Server (at least since SQL Server 2012).

You have essentially three options to do this correctly:

• Performing conversions outside of SQL Server and saving the results to a database
• Introduce timezone offset rules in a separate table and create stored procedures or UDFs to reference the rules table for performing transformations. You can find one approach to this approach in SQL Server Central (registration required)
• You can create SQL CLR UDF; I will describe the approach here.

While SQL Server does not offer tools for performing date conversion in time zone mode, the .NET platform works, and as long as you can use the SQL CLR, you can take advantage of this.

In Visual Studio 2012, make sure you have the data tools installed (otherwise the SQL Server project will not be displayed as an option) and create a new SQL Server project.

Then add the new SQL CLR C # User Defined Function, name it "ConvertToUtc". VS will generate a boiler stove for you, which 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 a few changes here. First, we want to return a SqlDateTime , not 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 stage we are ready to try. The easiest way is to use the built-in publishing tool in Visual Studio. Right-click the database project and select Publish. Set up the connection and the database name, and then click Publish to paste the code into the database or click Create Script if you want to save the script for posterity (or insert 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 5

Here is a tested procedure that updated my database from local to utc. The only input required to update the database is to enter the number of minutes, the local time of which is offset from the utc time in @Offset, and if the time zone is adjusted by adjusting daylight by setting @ApplyDaylightSavings.

For example, U.S. Central Time will enter @Offset = -360 and @ApplyDaylightSavings = 1 for 6 hours and yes apply daylight correction.

Database function support

---

 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 

Script Update

---

• Make a backup before running this script!
• Install @Offset and @ApplyDaylightSavings
• It is carried out only 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 6

If you need to convert dates other than today to different time zones, you have to deal with summer savings. I need a solution that could be made without worrying about the database version, without using stored functions and something that could be easily ported to Oracle.

I think Warren is on the right track with getting the right dates for daylight, but to make it more useful for several time zones and different rules for countries and even a rule that changed in the USA from 2006 to 2007, here's an option solutions. Please note that we have not only time zones, but also central Europe. Central Europe follows the last Sunday of April and the last Sunday of October. You will also notice that the USA in 2006 follows the old first Sunday in April, the last Sunday in October.

This SQL code may look a little ugly, but just copy and paste it into SQL Server and give it a try. Please note that there are 3 sections for many years, time zones and rules. If you want another year, just add it to the year of the union. The same goes 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 rules, use 1, 2, 3, or L for the first, second, third, or last Sunday. Part of the date 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 request in a view. Now, anytime I need a date with a timezone offset or converted to UTC, I just join this view and choose to get the date in a date format. Instead of datetime, I converted them 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 7

Here is my quick and dirty version. I know that all my dates were used in the eastern time zone of the USA. You can change the bias or otherwise make it more intelligent as you need. I did a one-time migration, so that was good enough.

 CREATE FUNCTION [dbo].[ConvertToUtc] ( @date datetime ) RETURNS DATETIME AS BEGIN -- Declare the return variable here DECLARE @utcDate datetime; DECLARE @offset int; SET @offset = (SELECT CASE WHEN @date BETWEEN '1987-04-05 02:00 AM' AND '1987-10-25 02:00 AM' OR @date BETWEEN '1988-04-03 02:00 AM' AND '1988-10-30 02:00 AM' OR @date BETWEEN '1989-04-02 02:00 AM' AND '1989-10-29 02:00 AM' OR @date BETWEEN '1990-04-01 02:00 AM' AND '1990-10-28 02:00 AM' OR @date BETWEEN '1991-04-07 02:00 AM' AND '1991-10-27 02:00 AM' OR @date BETWEEN '1992-04-05 02:00 AM' AND '1992-10-25 02:00 AM' OR @date BETWEEN '1993-04-04 02:00 AM' AND '1993-10-31 02:00 AM' OR @date BETWEEN '1994-04-03 02:00 AM' AND '1994-10-30 02:00 AM' OR @date BETWEEN '1995-04-02 02:00 AM' AND '1995-10-29 02:00 AM' OR @date BETWEEN '1996-04-07 02:00 AM' AND '1996-10-27 02:00 AM' OR @date BETWEEN '1997-04-06 02:00 AM' AND '1997-10-26 02:00 AM' OR @date BETWEEN '1998-04-05 02:00 AM' AND '1998-10-25 02:00 AM' OR @date BETWEEN '1999-04-04 02:00 AM' AND '1999-10-31 02:00 AM' OR @date BETWEEN '2000-04-02 02:00 AM' AND '2000-10-29 02:00 AM' OR @date BETWEEN '2001-04-01 02:00 AM' AND '2001-10-28 02:00 AM' OR @date BETWEEN '2002-04-07 02:00 AM' AND '2002-10-27 02:00 AM' OR @date BETWEEN '2003-04-06 02:00 AM' AND '2003-10-26 02:00 AM' OR @date BETWEEN '2004-04-04 02:00 AM' AND '2004-10-31 02:00 AM' OR @date BETWEEN '2005-04-03 02:00 AM' AND '2005-10-30 02:00 AM' OR @date BETWEEN '2006-04-02 02:00 AM' AND '2006-10-29 02:00 AM' OR @date BETWEEN '2007-03-11 02:00 AM' AND '2007-11-04 02:00 AM' OR @date BETWEEN '2008-03-09 02:00 AM' AND '2008-11-02 02:00 AM' OR @date BETWEEN '2009-03-08 02:00 AM' AND '2009-11-01 02:00 AM' OR @date BETWEEN '2010-03-14 02:00 AM' AND '2010-11-07 02:00 AM' OR @date BETWEEN '2011-03-13 02:00 AM' AND '2011-11-06 02:00 AM' OR @date BETWEEN '2012-03-11 02:00 AM' AND '2012-11-04 02:00 AM' OR @date BETWEEN '2013-03-10 02:00 AM' AND '2013-11-03 02:00 AM' OR @date BETWEEN '2014-03-09 02:00 AM' AND '2014-11-02 02:00 AM' OR @date BETWEEN '2015-03-08 02:00 AM' AND '2015-11-01 02:00 AM' OR @date BETWEEN '2016-03-13 02:00 AM' AND '2016-11-06 02:00 AM' OR @date BETWEEN '2017-03-12 02:00 AM' AND '2017-11-05 02:00 AM' OR @date BETWEEN '2018-03-11 02:00 AM' AND '2018-11-04 02:00 AM' OR @date BETWEEN '2019-03-10 02:00 AM' AND '2019-11-03 02:00 AM' OR @date BETWEEN '2020-03-08 02:00 AM' AND '2020-11-01 02:00 AM' OR @date BETWEEN '2021-03-14 02:00 AM' AND '2021-11-07 02:00 AM' THEN 4 ELSE 5 END); SELECT @utcDate = DATEADD(hh, @offset, @date) RETURN @utcDate; END 

Answer 8

If I didn’t miss something above (maybe), all of the above methods are wrong in that they don’t accept overlapping when switching from summer saving (like EDT) to standard time (like EST). A (very verbose) example:

 [1] EDT 2016-11-06 00:59 - UTC 2016-11-06 04:59 [2] EDT 2016-11-06 01:00 - UTC 2016-11-06 05:00 [3] EDT 2016-11-06 01:30 - UTC 2016-11-06 05:30 [4] EDT 2016-11-06 01:59 - UTC 2016-11-06 05:59 [5] EST 2016-11-06 01:00 - UTC 2016-11-06 06:00 [6] EST 2016-11-06 01:30 - UTC 2016-11-06 06:30 [7] EST 2016-11-06 01:59 - UTC 2016-11-06 06:59 [8] EST 2016-11-06 02:00 - UTC 2016-11-06 07:00 

Simple hour offsets based on date and time will not shorten it. If you do not know if the local time was recorded in EDT or EST from 01:00 to 01:59, you will not have a clue! For example, use 01:30: if you find later times in the range from 01:31 to 01:59 before that, you will not know if at 01:30 that you are watching, [3 or [6. In this case, you can get the correct UTC time with few encodings to look at previous entries (not fun in SQL), and this is the best case ...

Say you have the following local time recorded, and did not select a bit to indicate EDT or EST:

  UTC time UTC time UTC time if [2] and [3] if [2] and [3] if [2] before local time before switch after switch and [3] after [1] 2016-11-06 00:43 04:43 04:43 04:43 [2] 2016-11-06 01:15 05:15 06:15 05:15 [3] 2016-11-06 01:45 05:45 06:45 06:45 [4] 2016-11-06 03:25 07:25 07:25 07:25 

Times [2] and [3] can be in the 5 AM time frame, 6 AM time frame or one in 5 AM, and the other in 6 AM time frames. In other words: you fall and must erase all readings between 01:00:00 and 1:59:59 a.m. In this case, there is absolutely no way to decide the actual UTC time!

Answer 9

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

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

Answer 10

Depending on how far back you need to go, you can create a daylight saving time table and then join the table and perform a dst-sensitive conversion. This particular one is converted from EST to GMT (i.e. uses offsets 5 and 4).

 select createdon, dateadd(hour, case when dstlow is null then 5 else 4 end, createdon) as gmt from photos left outer join ( SELECT {ts '2009-03-08 02:00:00'} as dstlow, {ts '2009-11-01 02:00:00'} as dsthigh UNION ALL SELECT {ts '2010-03-14 02:00:00'} as dstlow, {ts '2010-11-07 02:00:00'} as dsthigh UNION ALL SELECT {ts '2011-03-13 02:00:00'} as dstlow, {ts '2011-11-06 02:00:00'} as dsthigh UNION ALL SELECT {ts '2012-03-11 02:00:00'} as dstlow, {ts '2012-11-04 02:00:00'} as dsthigh UNION ALL SELECT {ts '2013-03-10 02:00:00'} as dstlow, {ts '2013-11-03 02:00:00'} as dsthigh UNION ALL SELECT {ts '2014-03-09 02:00:00'} as dstlow, {ts '2014-11-02 02:00:00'} as dsthigh UNION ALL SELECT {ts '2015-03-08 02:00:00'} as dstlow, {ts '2015-11-01 02:00:00'} as dsthigh UNION ALL SELECT {ts '2016-03-13 02:00:00'} as dstlow, {ts '2016-11-06 02:00:00'} as dsthigh UNION ALL SELECT {ts '2017-03-12 02:00:00'} as dstlow, {ts '2017-11-05 02:00:00'} as dsthigh UNION ALL SELECT {ts '2018-03-11 02:00:00'} as dstlow, {ts '2018-11-04 02:00:00'} as dsthigh ) dst on createdon >= dstlow and createdon < dsthigh 

Answer 11

We can convert ServerZone DateTime to UTC and UTC to ServerZone DateTime

Just run the following scripts to understand the conversion, and then change as you need

 --Get Server TimeZone DECLARE @ServerTimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName',@ServerTimeZone OUT -- ServerZone to UTC DATETIME DECLARE @CurrentServerZoneDateTime DATETIME = GETDATE() DECLARE @UTCDateTime DATETIME = @CurrentServerZoneDateTime AT TIME ZONE @ServerTimeZone AT TIME ZONE 'UTC' --(OR) --DECLARE @UTCDateTime DATETIME = GETUTCDATE() SELECT @CurrentServerZoneDateTime AS CURRENTZONEDATE,@UTCDateTime AS UTCDATE -- UTC to ServerZone DATETIME SET @CurrentServerZoneDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE @ServerTimeZone SELECT @UTCDateTime AS UTCDATE,@CurrentServerZoneDateTime AS CURRENTZONEDATE 

Note. This ( AT TIME ZONE ) only works on SQL Server 2016+ , and this advantage automatically takes Daylight into account when converting to a specific time zone

Answer 12

Matt Johnson's answer is absolutely the best approach in Microsoft SQL Server 2016+. However, his proposal to use AT TIME ZONE has one vulnerability to be aware of. It is not possible to determine the correct offset from UTC for the hour when daylight saving time ends. As far as I know, there is no way to solve this problem. Consider the example below.

 --Assume that the value of input_date = '2018-11-04 01:00:00' --Assume that dates in this field are written in Central Standard Time select input_date at time zone 'Central Standard Time' >> '2018-11-04 01:00:00 -05:00' 

The default behavior of Microsoft SQL Server assumes that the offset to input_date is -05: 00. However, this time may actually represent an offset of -05: 00 OR -06: 00, depending on whether the DST has ended or not. There is no way to know for sure without an accompanying bias, so Microsoft SQL Server makes an assumption, which may or may not be correct.

Answer 13

In case someone stumbles upon this and uses Chris Barlow's answer, there will be a typo where 2018 is indicated twice, and the second time should be 2019. In particular, 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 excellent!