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

User · Answer

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

User · Answer

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  gt  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   lt  gt   sunday   SET  DSTStartDate   DATEADD day  1  DSTStartDate        --Get First Possible DST EndDate     IF   Year  gt  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   lt  gt   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  amp   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   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  gt   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   gt  DATEADD YEAR  -30  GETDATE      SELECT      SpringForward   CASE          WHEN MyDate  gt   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  gt   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

User · Answer

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

User · Answer

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  gt   dstlow and createdon  lt  dsthigh

User · Answer

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

User · Answer

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

User · Answer

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  gt   dstlow and createdon  lt  dsthigh then dst daylight else dst standard end pacificoffsettime       TODATETIMEOFFSET createdon  case when createdon  gt   dstlow and createdon  lt  dsthigh then dst daylight else dst standard end  pacifictime       SWITCHOFFSET TODATETIMEOFFSET createdon  case when createdon  gt   dstlow and createdon  lt  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

User · Answer

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

[sql-server] SQL Server - Convert date field to UTC

Examples related to sql-server

Examples related to datetime

Examples related to utc