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?
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);
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
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.
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
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.
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
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;
};
}
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).
SUBSTRING(CONVERT(VARCHAR(34), SYSDATETIMEOFFSET()), 29, 5)
Returns (for example):
-06:0
Not 100% positive this will always work.
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
