TSQL How to convert local time to UTC SQL Server 2008

Question

We are dealing with an application that needs to handle global time data from different time zones and daylight savings time settings  The idea is to store everything in UTC format internally and only convert back and forth for the localized user interfaces  Does the SQL Server offer any mechanisms for dealing with the translations given a time  a country and a timezone   This must be a common problem  so I m surprised google wouldn t turn up anything usable   Any pointers

User · Answer

Sample usage   SELECT     Getdate GETDATE        SysDateTimeOffset SYSDATETIMEOFFSET        SWITCHOFFSET SWITCHOFFSET SYSDATETIMEOFFSET   0       GetutcDate GETUTCDATE   GO   Returns   Getdate SysDateTimeOffset   SWITCHOFFSET    GetutcDate 2013-12-06 15 54 55 373 2013-12-06 15 54 55 3765498 -08 00  2013-12-06 23 54 55 3765498  00 00  2013-12-06 23 54 55 373

User · Answer

Yes  to some degree as detailed here   The approach I ve used  pre-2008  is to do the conversion in the  NET business logic before inserting into the DB

User · Answer

SQL Server 2008 has a type called datetimeoffset  It s really useful for this type of stuff   http   msdn microsoft com en-us library bb630289 aspx  Then you can use the function SWITCHOFFSET to move it from one timezone to another  but still keeping the same UTC value   http   msdn microsoft com en-us library bb677244 aspx  Rob

User · Answer

I tend to lean towards using DateTimeOffset for all date-time storage that isn t related to a local event  ie  meeting party  etc  12pm-3pm at the museum    To get the current DTO as UTC   DECLARE  utcNow DATETIMEOFFSET   CONVERT DATETIMEOFFSET  SYSUTCDATETIME    DECLARE  utcToday DATE   CONVERT DATE   utcNow   DECLARE  utcTomorrow DATE   DATEADD D  1   utcNow   SELECT   utcToday  today            utcTomorrow  tomorrow            utcNow  utcNow   NOTE  I will always use UTC when sending over the wire    client-side JS can easily get to from local UTC  See  new Date   toJSON        The following JS will handle parsing a UTC GMT date in ISO8601 format to a local datetime   if  typeof Date fromISOString     function         method to handle conversion from an ISO-8601 style string to a Date object       Date fromISOString  2009-07-03T16 09 45Z           Fri Jul 03 2009 09 09 45 GMT-0700   Date fromISOString   function input        var date   new Date input     EcmaScript5 includes ISO-8601 style parsing     if   isNaN date   return date         early shorting of invalid input     if  typeof input      string     input length  lt  10    input length  gt  40  return null       var iso8601Format       d 4  -  d 2  -  d 2      T    d 2     d 2      d 2       d 1 12              Zz    -     d 2       d 2               normalize input     var input   input toString   replace    s       replace   s              if   iso8601Format test input         return null    invalid format      var d   input match iso8601Format       var offset   0       date   new Date  d 1    d 2 -1   d 3    d 7     0   d 8     0   d 10     0  Math round    0      d 12     0     1000           use specified offset     if  d 13      Z   offset   0-date getTimezoneOffset        else if  d 13   offset     parseInt d 15  10    60     parseInt d 16  10       d 14      -     1   -1   - date getTimezoneOffset         date setTime date getTime      offset   60000         if  date getTime    lt   new Date -62135571600000  getTime       CLR DateTime MinValue       return null       return date

User · Answer

For SQL Server 2016 and newer  and Azure SQL Database  use the built in AT TIME ZONE statement   For older editions of SQL Server  you can use my SQL Server Time Zone Support project to convert between IANA standard time zones  as listed here   UTC to Local is like this   SELECT Tzdb UtcToLocal  2015-07-01 00 00 00    America Los Angeles     Local to UTC is like this   SELECT Tzdb LocalToUtc  2015-07-01 00 00 00    America Los Angeles   1  1    The numeric options are flag for controlling the behavior when the local time values are affected by daylight saving time   These are described in detail in the project s documentation

User · Answer

While a few of these answers will get you in the ballpark  you cannot do what you re trying to do with arbitrary dates for SqlServer 2005 and earlier because of daylight savings time  Using the difference between the current local and current UTC will give me the offset as it exists today  I have not found a way to determine what the offset would have been for the date in question   That said  I know that SqlServer 2008 provides some new date functions that may address that issue  but folks using an earlier version need to be aware of the limitations   Our approach is to persist UTC and perform the conversion on the client side where we have more control over the conversion s accuracy

User · Answer

This works for dates that currently have the same UTC offset as SQL Server s host  it doesn t account for daylight savings changes  Replace YOUR DATE with the local date to convert   SELECT DATEADD second  DATEDIFF second  GETDATE    GETUTCDATE     YOUR DATE

User · Answer

Here is the code to convert one zone DateTime to another zone DateTime  DECLARE  UTCDateTime DATETIME   GETUTCDATE    DECLARE  ConvertedZoneDateTime DATETIME   --  UTC  to  India Standard Time  DATETIME SET  ConvertedZoneDateTime    UTCDateTime AT TIME ZONE  UTC  AT TIME ZONE  India Standard Time  SELECT  UTCDateTime AS UTCDATE  ConvertedZoneDateTime AS IndiaStandardTime  --  India Standard Time  to  UTC  DATETIME SET  UTCDateTime    ConvertedZoneDateTime AT TIME ZONE  India Standard Time  AT TIME ZONE  UTC  SELECT  ConvertedZoneDateTime AS IndiaStandardTime  UTCDateTime AS UTCDATE   Note  AT TIME ZONE works only on SQL Server 2016  and the advantage is that it automatically considers Daylight when converting to a particular Time zone

User · Answer

SUBSTRING CONVERT VARCHAR 34   SYSDATETIMEOFFSET     29  5  Returns  for example   -06 0 Not 100  positive this will always work

User · Answer

7 years passed and    actually there s this new SQL Server 2016 feature that does exactly what you need  It is called AT TIME ZONE and it converts date to a specified time zone considering DST  daylight saving time  changes  More info here  https   msdn microsoft com en-us library mt612795 aspx

User · Answer

You can use GETUTCDATE   function to get UTC datetime Probably you can select difference between GETUTCDATE   and GETDATE   and use this difference to ajust your dates to UTC  But I agree with previous message  that it is much easier to control right datetime in the business layer  in  NET  for example

[sql-server] TSQL: How to convert local time to UTC? (SQL Server 2008)

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to timezone

Examples related to utc