DateTimeOffset example in SQL 2008R2

Question

I have doubts about using datetimeoffsetSQL Server 2008 r2.

Suppose I have a web application, a db server in Spain and a client in Canada. I do not know the time zone between Spain and Canada, but suppose that it is 5 hours more in Spain. Thus, a user in Canada wants to add a new employee at 23:00, when he saves, he calls the stored procedure, which inside calls the function SYSDATETIMEOFFSET()to populate a column CreatedDatethat has a data type datetimeoffset(7).

In this case, what time will be stored in the database? and how do I do to show the correct time and time for a user in Canada who wants to check CreatedDate? Is there a good example to test this?

Answer 1

According to MSDN:

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

In your example, the data will be stored in binary format, which can be converted to 14 Nov 2013 23:00 -5:00, which means a date and time that are local, as well as a time offset from -5 hours to UTC (suppose this is for Canada).

When you store this type of value, you must consider the offset yourself; the system does not do this automatically.

UTC, , .. , .

, .

datetimeoffset MSDN.

---

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

,

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