Introduction

This article will introduce you to the new Datetimeoffset data type and show how it can be used to both validate UTC (Coordinated Universal Time) datetime values and also to compare datetime values across timezones.

The Datetimeoffset Data Type

The Datetimeoffset data type was introduced in SQL Server 2008 (and .Net Framework 3.5) and is the most advanced datetime date type available. It can store high precision datetime values and also the offset from UTC. It is also the data type of the date parameters required by most SQL Server 2008 system functions, such as DATEDIFF, DATEPART, etc.

So let's take a simple example of declaring and assigning a datetimoffset variable to demonstrate the component parts of the data type.

declare @utcDatetime datetimeoffset
declare @nycDatetime datetimeoffset
set @utcDatetime = CAST('2011-03-01 05:00:00 +00:00' AS datetimeoffset)
set @nycDatetime = CAST('2011-03-01 05:00:00 -05:00' AS datetimeoffset)
select @utcDatetime, @nycDatetime

This is much the same as declaring a standard datetime variable with the obvious benefit of being able to store the timezone offset along with the date. In the above example, the first variable has a timezone offset of zero (UTC), whereas the second one identifies that this time is five hours behind UTC (the current time difference between NYC and the UK).

As the majority of my systems have a global user base and also store UTC dates, the Datetimeoffset data type is rapidly becoming the default for me. That said, I don't use it for every datetime column and use the following logical rules for choosing which datetime data type to use (actually Anthony Moore's excellent C# guidance adapted for SQL Server - see Justin Van Patten article in the links at the end of the article):

• Datetimeoffset whenever the data will refer to an exact point in time - transaction/added/updated datetimes (UTC) and localized datetimes.
• Datetime for any cases where the time is irrelevant across time zones or the timezone is simply not known - imported data, standard datetimes that are the same across timezones, etc.
• Datetime with a 00:00:00 time to represent whole dates - dates of birth
• Time for just times of the day where the date is irrelevant - opening hours, etc.

So what are the benefits of this new data type? Well, let's see some practical examples of it in use and you can judge for yourselves.

UTC Datetime Columns

Many of us will have columns that store UTC datetimes and, as we have no way of checking that the datetime being passed in actually is UTC, we just have to assume that it is. With the new Datetimeoffset data type storing a UTC offset, we are now able to validate this.

You will notice from the below code that the DATEPART Function can now detect the timezone offset. This is because this function, as with most system date functions in SQL Server 2008, uses the Datetimeoffset Data Type for the date parameters. The code shows how you can add a check constraint to check that the datetimeoffset being passed in is actually UTC, by checking that the timezone offset is zero.

-- UTC datatimeoffset column constraint (must be UTC)
CREATE TABLE dbo.dtOffsetConstraintTest(utcUpdatedDate datetimeoffset(7) not null);
ALTER TABLE dbo.dtOffsetConstraintTest
 ADD CONSTRAINT CK_utcUpdatedDate
 CHECK (DATEPART(TZOFFSET, utcUpdatedDate) = 0);
-- will insert fine
INSERT INTO dbo.dtOffsetConstraintTest(utcUpdatedDate) values(CAST('2011-03-01 12:35:29.1234567 +00:00' AS datetimeoffset(7)));
-- will fail the check constraint as not UTC
BEGIN TRY
 INSERT INTO dbo.dtOffsetConstraintTest(utcUpdatedDate) values(CAST('2011-03-01 12:35:29.1234567 +12:15' AS datetimeoffset(7)))
END TRY
BEGIN CATCH
 -- in reality we would raise the error here but for demo purposes we will just display it
 SELECT ERROR_NUMBER(),
 CASE WHEN ERROR_NUMBER() = 547 -- check constraint violation
 THEN 'This is not a UTC date.' -- display a custom error description for check constraint violations
 ELSE ERROR_MESSAGE()
 END
END CATCH;
SELECT * FROM dbo.dtOffsetConstraintTest;
DROP TABLE dbo.dtOffsetConstraintTest;
GO

Datetime Comparison across Different Timezones

To demonstrate the real power of the Datetimeoffset Data Type, we can use a simple example using the DATEDIFF function. Taking a UTC datetime and a localised datetime, we will calculate the difference, in minutes, between the dates.

Example one will use the Datetime data type. Obviously, since this data type cannot store the timezone offset, these have to be held in variables and in most cases will the result of a lookup to a table that holds this information or separate column values stored alongside the datetime.

Example two will use the Datetimeoffset data type. As this data type stores the timezone offset as well as the datetime, no variables are required.

Example One: Using the Datetime Data Type

Here we will compare a UTC datetime with a localised Vancouver datetime. The timezone offset from UTC is stored in two variables, one holding the hour and the other the minutes.

-- compare a UTC date with a localised Vancouver date (UTC -8) without using datetimeoffsets
declare @utcDatetime datetime
declare @comparisonDate datetime
declare @utcHourOffset int
declare @utcMinuteOffset int
set @utcDatetime = CAST('2011-03-01 05:00:00' AS datetime)
set @comparisonDate = CAST('2011-03-01 06:00:00' AS datetime)
set @utcHourOffset = 8
set @utcMinuteOffset = 0
-- compare the dates (UTC date vs. the comparison date + UTC hour and minute offsets)
select DATEDIFF(minute, @utcDatetime, 
 dateadd(minute, @utcMinuteOffset, dateadd(hour, @utcHourOffset, @comparisonDate))) as minutesToStart
GO

Example Two: Using the Datetimeoffset Data Type

Here we have no need for variables to hold the UTC offsets, as this date is held in the data type along with with the datetime.

-- now do the same query but with datetimeoffsets
declare @utcDatetime datetimeoffset
declare @comparisonDate datetimeoffset
set @utcDatetime = CAST('2011-03-01 05:00:00 -00:00' AS datetimeoffset)
set @comparisonDate = CAST('2011-03-01 06:00:00 -08:00' AS datetimeoffset)
-- compare the dates (UTC date vs. the comparison date + UTC hour and minute offsets)
select DATEDIFF(minute, @utcDatetime, @comparisonDate) as minutesToStart
GO

As you can see, the code in example two is far cleaner and uses two less system function calls (as there is no need for any DATEADD calls).

I personally have made good use of the Datetimeoffset Data Type in conjunction with the DATEDIFF system function already. In one of my global applications, the users have tasks that must be completed during a given timeframe. These tasks have a status of "Not Started" prior to the start date and time being reached. Now the update of this status is controlled by a server in the UK, so I had to rely heavily on an ISO timezone lookup table to correctly set the status of a task in say Japan, to start when it actually is the correct time on the given day in Japan, not when it is that time on the same day in the UK. Storing the UTC offset with the date has been a great help here and ensures both faster processing and greater accuracy. I also have many UTC columns that I store "updated" dates in and having a check constraint on these to ensure that the dates passed in actually are UTC has also been invaluable.

Conclusion

The Datetimeoffset data type uniquely identifies a point in time, without the need for any related hour or minute UTC offset values. This in itself is reason enough to start using it. On top of this, as previously mentioned, most SQL Server 2008 system functions take Datetimeoffsets for the date parameters, so you will save on the cost of the implicit conversions done if you currently use datetimes and make heavy use of date functions such as DATEDIFF, DATEPART, etc. Plus, if your use .Net 3.5+, then the migration from Datetime to Datetimeoffset is painless (see links below).

Hopefully this article has given you something to think about and, if you were not already aware of this new data type, has made you want to investigate it in more detail. There is a link below to the official MSDN page on the data type and three others that show how to deal with this new data type in .Net.

Related Links

SQL Server 2008 Datetimeoffset Data Type (MSDN): http://msdn.microsoft.com/en-us/library/bb630289.aspx

Choosing Between DateTime, DateTimeOffset, and TimeZoneInfo (MSDN .Net Framework 4): http://msdn.microsoft.com/en-us/library/bb384267.aspx#Y603

Dan Rigsby - DateTime vs. DateTimeOffset in .Net (.Net Framework 3.5): http://www.danrigsby.com/blog/index.php/2008/08/23/datetime-vs-datetimeoffset-in-net/

DateTimeOffset: A New DateTime Structure in .NET 3.5 by Justin Van Patten (C#): http://csharpfeeds.com/post/5395/DateTimeOffset_A_New_DateTime_Structure_in_.NET_3.5_Justin_Van_Patten.aspx