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

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?

SUBSTRING(CONVERT(VARCHAR(34), SYSDATETIMEOFFSET()), 29, 5)

Returns (for example):

-06:0

Not 100% positive this will always work.

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.

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.

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 < 10 || input.length > 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() <= new Date(-62135571600000).getTime()) // CLR DateTime.MinValue
      return null;

    return date;
  };
}

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

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

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.

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

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

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