DateTimeOffset Sample in SQL 2008R2

Question

I have a doubt related to using datetimeoffset in SQL server 2008 r2.

Suppose I have a web application, the db server in Spain and the client is in Canada. I don't know the time zone between Spain and Canada but suppose its 5 hours more in Spain. So user in Canada wants to add a new Employee at 23:00 PM, when clicks save, it calls a stored procedure that inside call the function SYSDATETIMEOFFSET() to fill the column CreatedDate that has a datatype datetimeoffset(7).

In this case, what will be the datetime saved in the database? and How should I do to show the right datetime to the user in Canada that wants to check the CreatedDate? Is there any good example to check this?

Answer 1

According to MSDN:

The data is stored in the database and processed, compared, sorted, and indexed in the server as in UTC. The time zone offset will be preserved in the database for retrieval.

In your example, the data will be stored in a binary format that can be translated to 14 Nov 2013 23:00 -5:00, which means the date and time that is local plus the time offset of -5 hours to UTC (let's suppose this is the offset for Canada).

When you store this type of value, you have to provide the offset yourself, the system doesn't do it automatically.

Because the data is stored as UTC time, it makes comparison, sorting, etc. of data easy, while you can always retrieve the original time offset.

You should generally store client's local time with offset when storing information about events that need to be compared across time zones.

More on datetimeoffset on MSDN.

---

Example

Create a table and insert data

create table dto (dto datetimeoffset(7))

insert into dto values (GETDATE()) -- inserts date and time with 0 offset
insert into dto values (SYSDATETIMEOFFSET()) -- current date time and offset
insert into dto values ('20131114 08:54:00 +10:00') -- manual way

When I select the data, I get

2013-11-14 07:56:17.2300000 +00:00 -- current time, no offset so useless in this case
2013-11-14 07:56:17.2338125 +11:00 -- current time with my local offset (in Australia)
2013-11-14 08:54:00.0000000 +10:00 -- manually inserted data