Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The SQL Server 2008 Datetimeoffset Data Type


The SQL Server 2008 Datetimeoffset Data Type

Author
Message
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 659
Comments posted to this topic are about the item The SQL Server 2008 Datetimeoffset Data Type

James
MCM [@TheSQLPimp]
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4343 Visits: 72513

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


Since you're using Time for the time of day where date is irrelevant,

Why are you not using the Date data type for Dates where time is irrelevant (such as dates of birth)?



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 659
Good point Mark, Date would be better here as the time portion is irrelevant. I don't tend to store many dates like this but the Date data type would be preferred over Datetime.

James
MCM [@TheSQLPimp]
Kendal Van Dyke
Kendal Van Dyke
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 983
One downside of datetimeoffset is that it's not easy to switch to different timezones without understanding the nuances of Daylight Saving Time (though to be fair it's not easy with any other datetime format in SQL Server either).

For example, if I want to convert both 2011-03-12 07:30:00.0000000 +00:00 and 2011-03-14 07:30:00.0000000 +00:00 to Eastern time I need to understand that the offset for the 12th is 5 hours but the offset for the 14th is 4 hours. And what about geographic areas that don't observe DST? Does a -7 hour offset represent Phoenix at an time in the year or Seattle only during DST?

It's unfortunate that system functions for timezone awareness were not included with the introduction of datetimeoffset, especially considering that SQL Server has the spatial data type and this is something that's built into .NET, Java, and most other platforms\languages. It would be incredibly useful to supply a datetime value, a spatial value (or a timezone name), and get a datetimeoffset with the correct offset in return.

Erland Sommarskog submitted a Connect feature request for this (http://connect.microsoft.com/SQLServer/feedback/details/293933/add-a-set-timezone-command). Please consider voting for it to be included in a future version of SQL Server.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Misha_SQL
Misha_SQL
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 995
Thank you for the article! I didn't even know this feature existed Blush I suppose it wasn't publicized enough. It's incredibly useful, but, as Kendal pointed out, handling DST changes remains a challenge.



James A Skipwith
James A Skipwith
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 659
Thanks all, I too was unaware of this data type but have now put it into use. I agree that DST is a pain. The timezones in .net know that they are DST affected but still return the time without this factored in. Nice. As I say, I stumbled across this and it saved me a whole heap of pain but Microsoft do need to handle the DST issue or else we will still have to code around this in some way.

James
MCM [@TheSQLPimp]
vreten
vreten
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 4
This is what I came up with, it converts the start time from UTC to whatever the local time is running on the sql server it is dynamic and should work on any sql server in any timezone. What is nice is when the server goes to DST this will also return the DST time.

Print CONVERT(char(10), dateadd( hh, datepart(hour, GetDate()-GetUTCDate() ) - 24, '2011-04-22 17:30:00.000') , 108) as 'Activity Start Time'
Kendal Van Dyke
Kendal Van Dyke
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 983
vreten (4/18/2011)
This is what I came up with, it converts the start time from UTC to whatever the local time is running on the sql server it is dynamic and should work on any sql server in any timezone. What is nice is when the server goes to DST this will also return the DST time.

Print CONVERT(char(10), dateadd( hh, datepart(hour, GetDate()-GetUTCDate() ) - 24, '2011-04-22 17:30:00.000') , 108) as 'Activity Start Time'


This works if the date you're looking at is for an event that occurs in the same time zone as the server and does not cross the boundry between the start and end dates & times for DST.

A scenario where the problem still exists is let's say today I want to convert an Eastern time zone date from Jan 1, 2011 to UTC. Today's offset is -4 hours but the proper offset for the date I'm looking at is -5 hours. Using the current offset for the server when I analyze the date would mean my conversion is off by an hour.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/
vreten
vreten
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 4
Kendal Van Dyke (4/18/2011)
vreten (4/18/2011)
This is what I came up with, it converts the start time from UTC to whatever the local time is running on the sql server it is dynamic and should work on any sql server in any timezone. What is nice is when the server goes to DST this will also return the DST time.

Print CONVERT(char(10), dateadd( hh, datepart(hour, GetDate()-GetUTCDate() ) - 24, '2011-04-22 17:30:00.000') , 108) as 'Activity Start Time'


This works if the date you're looking at is for an event that occurs in the same time zone as the server and does not cross the boundry between the start and end dates & times for DST.

A scenario where the problem still exists is let's say today I want to convert an Eastern time zone date from Jan 1, 2011 to UTC. Today's offset is -4 hours but the proper offset for the date I'm looking at is -5 hours. Using the current offset for the server when I analyze the date would mean my conversion is off by an hour.


Yeah, I had the opposite problem, UTC in the DB that needed to be converted the local timezone. It would be cool if GetDate could be overriden to accept standard or daylight parameter and perform the offset automatically.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4343 Visits: 72513
Make a table...


CREATE TABLE [Daylightsavings](
[PK_dst_key] [int] IDENTITY(1,1) NOT NULL,
[dst_year] [int] NULL,
[dst_begin] [date] NULL,
[dst_end] [date] NULL,
[dst_diff] [int] NULL,
PRIMARY KEY CLUSTERED
(
[PK_dst_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



INSERT the daylight savings start and stop times plus the offset to use.


insert into Daylightsavings
(dst_year,dst_begin,dst_end,dst_diff)
values
(1980,'1980-04-27','1980-10-26',1),
(1981,'1981-04-26','1981-10-25',1),
(1982,'1982-04-25','1982-10-31',1),
(1983,'1983-04-24','1983-10-30',1),
(1984,'1984-04-29','1984-10-28',1),
(1985,'1985-04-28','1985-10-27',1),
(1986,'1986-04-27','1986-10-26',1),
(1987,'1987-04-05','1987-10-25',1),
(1988,'1988-04-03','1988-10-30',1),
(1989,'1989-04-02','1989-10-29',1),
(1990,'1990-04-01','1990-10-28',1),
(1991,'1991-04-07','1991-10-27',1),
(1992,'1992-04-05','1992-10-25',1),
(1993,'1993-04-04','1993-10-31',1),
(1994,'1994-04-03','1994-10-30',1),
(1995,'1995-04-02','1995-10-29',1),
(1996,'1996-04-07','1996-10-27',1),
(1997,'1997-04-06','1997-10-26',1),
(1998,'1998-04-05','1998-10-25',1),
(1999,'1999-04-04','1999-10-31',1),
(2000,'2000-04-02','2000-10-29',1),
(2001,'2001-04-01','2001-10-28',1),
(2002,'2002-04-07','2002-10-27',1),
(2003,'2003-04-06','2003-10-26',1),
(2004,'2004-04-04','2004-10-31',1),
(2005,'2005-04-03','2005-10-30',1),
(2006,'2006-04-02','2006-10-29',1),
(2007,'2007-03-11','2007-11-04',1),
(2008,'2008-03-09','2008-11-02',1),
(2009,'2009-03-08','2009-11-01',1),
(2010,'2010-03-14','2010-11-07',1),
(2011,'2011-03-13','2011-11-06',1),
(2012,'2012-03-11','2012-11-04',1),
(2013,'2013-03-10','2013-11-03',1),
(2014,'2014-03-09','2014-11-02',1),
(2015,'2015-03-08','2015-11-01',1),
(2016,'2016-03-13','2016-11-06',1),
(2017,'2017-03-12','2017-11-05',1),
(2018,'2018-03-11','2018-11-04',1),
(2019,'2019-03-10','2019-11-03',1),
(2020,'2020-03-08','2020-11-01',1),
(2021,'2021-03-14','2021-11-07',1),
(2022,'2022-03-13','2022-11-06',1),
(2023,'2023-03-12','2023-11-05',1),
(2024,'2024-03-10','2024-11-03',1),
(2025,'2025-03-09','2025-11-02',1)


Now JOIN to this table



LEFT JOIN Daylightsavings dst ON
<somedatevalue> between dst.begin and dst.end

Then use isnull

somedatevalue = dateadd(hh,isnull(dst.dst_diff,0),somedatevalue)






--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search