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?

Are these times all "local" to you, or are they all different times from multiple time zones? — SqlRyan, Apr 23 '10 at 18:19
possible duplicate of [TSQL: How to convert local time to UTC? (SQL Server 2008)](http://stackoverflow.com/questions/1205142/tsql-how-to-convert-local-time-to-utc-sql-server-2008) — Reinstate Monica Please, Mar 06 '15 at 02:38

score 91 · Answer 1 · answered Jun 19 '11 at 16:48

I do not believe the above code will work. The reason is that it depends upon 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 will, if run now, add 7 hours to every date which is desired to be converted. But if a historical stored date, or a date in the future, is converted, and that date is not during daylight savings time, it will still add 7, when the correct offset is 8. Bottom line: you cannot convert date/times properly between time zones (including UTC, which does not obey daylight savings time) by only looking at the current date. You must consider the date itself that you are converting, as to whether daylight time was in force on that date. Furthermore, the dates at which daylight and standard times change themselves have changed (George Bush changed the dates during his administration for the USA!). In other words, any solution which even references getdate() or getutcdate() does not work. It must parse the actual date to be converted.

You could create a CLR-function that will use C# to convert timezones taking daylight saving into account http://www.jitbit.com/maxblog/17-sql-server-how-to-convert-datetime-to-utc/ — Alex from Jitbit, Aug 11 '14 at 19:55
Everything stated here is correct. My new post below, 5 years after this one, takes all these issues into account in a native SQL Server user defined function. — Ben Gripka, Nov 02 '15 at 21:13

Matt Johnson-Pint · Answer 2 · 2016-11-22T18:12:46.710

78

With SQL Server 2016, there is now built-in support for time zones with the AT TIME ZONE statement. You can chain these to do conversions:

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

Or, this would work as well:

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

Either of these will interpret the input in Pacific time, properly account for whether or not DST is in effect, and then convert to UTC. The result will be a datetimeoffset with a zero offset.

More examples in the CTP announcement.

edited Nov 22 '16 at 18:12

answered Dec 07 '15 at 00:40

Matt Johnson-Pint

230,703
74
448
575

2

Why at Pacific Standard Time first? – xr280xr Aug 07 '17 at 22:31
@xr280xr - This does conversion from a specific time zone to UTC. Pacific is just an example here. – Matt Johnson-Pint Aug 07 '17 at 23:39
This solution is only for SQL server 2016 and above – Aakash Uniyal May 08 '19 at 10:10
3

This approach does have one minor limitation to be aware of. There is no way to determine the correct offset from UTC for the hour in which when DST ends. Consider that '2018-11-04 01:00:00' is a datetime that was written from Central Standard Time. The 01:00:00 hour could actually represent a -05:00 OR -06:00 offset from UTC, depending on whether it was written before or after DST had ended. There's no way to know for sure without an accompanying offset. Microsoft SQL Server behavior is to use an -05:00 offset, which may or may not be correct. – Corbin Jul 24 '19 at 19:59
1

@Corbin - Agreed, it is a limitation. I tackled that explicitly with my [SQL Server Time Zone Support](https://github.com/mj1856/SqlServerTimeZoneSupport) project (see the two trailing parameters on `LocalToUtc` and `ConvertZone`), but the built-in `AT TIME ZONE` doesn't allow control over this aspect. – Matt Johnson-Pint Jul 24 '19 at 21:00

SqlRyan · Accepted Answer · 2010-04-23T20:44:22.137

36

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?

edited Apr 23 '10 at 20:44

answered Apr 23 '10 at 20:05

SqlRyan

33,116
33
114
199

Looks the part, will give it a go – James Apr 23 '10 at 20:14
I am getting `Argument data type datetime is invalid for argument 2 of dateadd function.` Any Ideas? – James Apr 23 '10 at 20:16
2

@James: you need to reverse the second and third parameter in the call: `DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)` -- the second parameter is the interval (the number of hours to add or subtract), and the third is the value to apply that interval to – marc_s Apr 23 '10 at 20:41
@James and Marc_s: Crap - I always get those backwards. Marc is correct - it's DATEADD(interval, AmountToAdd, startingDatetime). I've edited the answer. – SqlRyan Apr 23 '10 at 20:43
62

No! The difference is dependent on the exact date. It depends on daylight-savings. – usr Oct 17 '11 at 14:56
16

This doesn't account for daylight savings. See the answer below by Roderick Llewellyn. – Tom Hamming Jul 11 '12 at 23:23
The answer by Ben Gripka actually accounts for daylight savings so it is better answer – jandic Jun 01 '16 at 15:38
Be aware that the solution should be using "minute" instead of "hour"/"hh" in both spots. Some timezones aren't whole-hour offsets. E.g. India is UTC+5:30. ( https://www.timeanddate.com/time/time-zones-interesting.html ) – Granger Sep 12 '19 at 19:05

Michael Teper · Answer 4 · 2015-04-01T07:15:58.800

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:

Perform the conversion outside of SQL Server and store results in the database
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)
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)

I don't think this will work for ALL historical dates "The ToUniversalTime method recognizes only the current daylight saving time adjustment rule for the local time zone. As a result, it is guaranteed to accurately return the Coordinated Universal Time (UTC) corresponding to a particular local time only during the period in which the latest adjustment rule is in effect. It may return inaccurate results if time is a historic date and time value that was subject to a previous adjustment rule." — oatsoda, Jun 06 '14 at 14:32
You probably need to use [TimeZone.IsDaylightSavingTime](http://msdn.microsoft.com/en-us/library/c1f14e97.aspx) and handle the conversion yourself. " Assuming that the operating system itself has accurate historic daylight saving time data, a more accurate result is available by using the TimeZoneInfo.IsDaylightSavingTime method. Whenever possible, use the TimeZoneInfo.IsDaylightSavingTime method." — oatsoda, Jun 06 '14 at 14:53
After .NET 4.0, you can use `sqlLocalValue.Value.ToUniversalTime()` and get the correct answer. See https://stackoverflow.com/a/31234152/1152054 — Dan Friedman, May 11 '23 at 15:04

score 13 · Answer 5 · answered Nov 01 '15 at 05:33

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

Make a backup before running this script!
Set @Offset & @ApplyDaylightSavings
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

score 5 · Answer 6 · edited Mar 17 '20 at 04:24

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 '2019' 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'

So would this account for previous date/time fields in the past? or just what is currently the rules? — Joshua F. Rountree, Nov 06 '14 at 15:45
Do you have an example of your second query that doesn't use 2008+ stuff. My database is in 2005. Any tips? — Joshua F. Rountree, Nov 06 '14 at 15:53
@JoshuaRountree, The first view is the most important part and I don't think it uses anything that 2005 doesn't have. I assume the offsets are your problem. Try removing those columns. The first may be what you need and there are other ways to use it as long as the you first view that does the conversion. — Chris Barlow, Nov 06 '14 at 23:08

score 3 · Answer 7 · answered Sep 07 '16 at 19:10

Here's my quick and dirty version. I know all of my dates were using the US Eastern time zone. You can change the offset or otherwise make it smarter as you need to. I was doing a one-time migration so this 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

score 1 · Answer 8 · answered May 19 '16 at 22:54

Unless I missed something above (possible), all of the methods above are flawed in that they don't take the overlap when switching from daylight savings (say EDT) to standard time (say EST) into account. 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 won't cut it. If you don't know if the local time was recorded in EDT or EST between 01:00 and 01:59, you won't have a clue! Let's use 01:30 for example: if you find later times in the range 01:31 through 01:59 BEFORE it, you won't know if the 01:30 you're looking at is [3 or [6. In this case, you can get the correct UTC time with a bit of coding be looking at previous entries (not fun in SQL), and this is the BEST case...

Say you have the following local times recorded, and didn't dedicate 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] may be in the 5 AM timeframe, the 6 AM timeframe, or one in the 5 AM and the other in the 6 AM timeframe . . . In other words: you are hosed, and must throw out all readings between 01:00:00 and 01:59:59. In this circumstance, there is absolutely no way to resolve the actual UTC time!

score 0 · Answer 9 · answered Dec 03 '12 at 04:19

0

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')

answered Dec 03 '12 at 04:19

Gaurav Arora

25
1

2

Isn't this the same as @SqlRyan's answer? This seems to have the same problem with daylight savings time. – Slider345 Dec 04 '12 at 14:26
Yup, what @Slider345 said. – Michael Teper Jan 21 '13 at 21:29
No. @SqlRyan's doesn't account for the fact that timezones are **minute** offsets. E.g. India is UTC+5:30. ( https://www.timeanddate.com/time/time-zones-interesting.html ) – Granger Sep 12 '19 at 19:02

score 0 · Answer 10 · answered Jun 09 '14 at 03:07

Depending on how far back you need to go, you can build a table of daylight savings times and then join the table and do a dst-sensitive conversion. This particular one converts from EST to GMT (i.e. uses offsets of 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

KarthikeyanMlp · Answer 11 · 2017-07-06T11:32:32.143

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

Simply run the following scripts to understand the conversion then modify as what you need

--Get Server's 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) working on only SQL Server 2016+ and this advantage is automatically considering Daylight while converting to particular Time zone

score 0 · Answer 12 · answered Mar 17 '20 at 01:35

I'm a bit late to the game but I needed to do something like this on SQL 2012, I haven't fully tested it yet but here is what I came up with.

CREATE FUNCTION SMS.fnConvertUTC
(
    @DateCST datetime
)
RETURNS DATETIME
AS
BEGIN
    RETURN 
        CASE 
        WHEN @DateCST 
            BETWEEN 
                CASE WHEN @DateCST > '2007-01-01' 
                THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-MAR-14 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-MAR-14 02:00' ) + 1
                ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-APR-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-APR-07 02:00' ) + 1 END
            AND
                CASE WHEN @DateCST > '2007-01-01' 
                THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-NOV-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-NOV-07 02:00' ) + 1
                ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-OCT-31 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-OCT-31 02:00' ) + 1 END
        THEN DATEADD(HOUR,4,@DateCST)
        ELSE DATEADD(HOUR,5,@DateCST) 
        END
END

Above someone posted a static list DST dates so I wrote the below query to compare this code's output to that list... so far it looks correct.

;WITH DT AS 
( 
    SELECT MyDate = GETDATE() 
    UNION ALL 
    SELECT MyDate = DATEADD(YEAR,-1,MyDate) FROM DT
    WHERE DATEADD(YEAR,-1,MyDate) > DATEADD(YEAR, -30, GETDATE())
)
SELECT 
    SpringForward = CASE 
        WHEN MyDate > '2007-01-01' 
        THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-MAR-14 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-MAR-14 02:00' ) + 1
        ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-APR-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-APR-07 02:00' ) + 1 END
,   FallBackward  = CASE 
        WHEN MyDate > '2007-01-01' 
        THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-NOV-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-NOV-07 02:00' ) + 1
        ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-OCT-31 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-OCT-31 02:00' ) + 1 END
FROM DT
ORDER BY 1 DESC

SpringForward      FallBackward
----------------   ----------------
2020-03-08 02:00   2020-11-01 02:00
2019-03-10 02:00   2019-11-03 02:00
2018-03-11 02:00   2018-11-04 02:00
2017-03-12 02:00   2017-11-05 02:00
2016-03-13 02:00   2016-11-06 02:00
2015-03-08 02:00   2015-11-01 02:00
2014-03-09 02:00   2014-11-02 02:00
2013-03-10 02:00   2013-11-03 02:00
2012-03-11 02:00   2012-11-04 02:00
2011-03-13 02:00   2011-11-06 02:00
2010-03-14 02:00   2010-11-07 02:00
2009-03-08 02:00   2009-11-01 02:00
2008-03-09 02:00   2008-11-02 02:00
2007-03-11 02:00   2007-11-04 02:00
2006-04-02 02:00   2006-10-29 02:00
2005-04-03 02:00   2005-10-30 02:00
2004-04-04 02:00   2004-10-31 02:00
2003-04-06 02:00   2003-10-26 02:00
2002-04-07 02:00   2002-10-27 02:00
2001-04-01 02:00   2001-10-28 02:00
2000-04-02 02:00   2000-10-29 02:00
1999-04-04 02:00   1999-10-31 02:00
1998-04-05 02:00   1998-10-25 02:00
1997-04-06 02:00   1997-10-26 02:00
1996-04-07 02:00   1996-10-27 02:00
1995-04-02 02:00   1995-10-29 02:00
1994-04-03 02:00   1994-10-30 02:00
1993-04-04 02:00   1993-10-31 02:00
1992-04-05 02:00   1992-10-25 02:00
1991-04-07 02:00   1991-10-27 02:00

(30 row(s) affected)

SQL Server - Convert date field to UTC

12 Answers12

Linked