|
|
|
SSC Journeyman
 
Group: General Forum Members
Last Login: Tuesday, October 27, 2009 2:28 AM
Points: 79,
Visits: 148
|
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, August 18, 2009 6:16 PM
Points: 131,
Visits: 89
|
|
"datimeoffset" stores both date and time components and the time zone offset, ranging from 1st January 0001 to 31st December 9999, with accuracy of 100 ns, for example: "2007-10-14 19:35:09.3579284 +02:15. The time zone offset ranges from -14:00 to +14:00.
How does sql server store this datetimeoffset datatype? Does the offset time indicate how the displayed time should be modified to show the universal time?
Do you know what implicit conversion ss2k8 performs when you attempt to convert a DatetimeOffset to a normal datetime? Does it add/minus the offset time or does it only return the datetime component?
I'd check this myself but I dont have the CTP.
I am glad that ss2k8 is providing these extra date fields. That datetimeoffset is of particular interest as it will be great to display and store the local time while also being aware of what time offset was used at the time. Very handy when daylight savings occur and you want to know the difference between 2:30am before daylight savings stopped and 2:30am after daylight savings stopped.
-- JP
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 05, 2009 8:12 AM
Points: 2,
Visits: 15
|
|
| The article says: "The first two are the same as in SQL Server 2000 and 2005. "datetime" stores both date and time components, ranging from 1st January 1753 to 31st December 1999". There is a typo here I guess? isn't it 31st December 9999?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, January 21, 2009 10:45 AM
Points: 188,
Visits: 27
|
|
Hi
Fantastic article and a good detailed look at an important area of functionality. Thanks for your time!
A couple of comments:
- Third paragraph from the end, it says "CURRENT_TIMESTAMP function is equivalent to GETDATE function" - should this not read CURRENT_DATEIMTE function...?
- Can the storage sizes of these datatypes be included in the article? I didn't manage to download the Books Online yet for SQL 2008 otherwise I'd include them here :-(
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Monday, December 03, 2007 3:34 AM
Points: 99,
Visits: 10
|
|
| Yuk. Hope they change the name of data type DATETIME2. I just hate that.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 5:32 AM
Points: 158,
Visits: 199
|
|
Storage size for new date and time data types are (in bytes):
date 3
time 3-5
datetime2 6-8
datetimeoffset 8-10
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 5:32 AM
Points: 158,
Visits: 199
|
|
Talking about date and time data types it's also interesting to mention new function SWITCHOFFSET which returns a datetimeoffset that is changed from the stored offset to a new time zone offset (see BOL).
Datitime2 is typical Oracle naming convention for new added types and I don't like it too :)
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 28, 2009 1:33 PM
Points: 139,
Visits: 31
|
|
Nice article.
Are there any new format options that go along with the new data types?
So when we currently say Convert(varchar,DATETIME,112) are there new options for
Convert(varchar,TIME,xxx)? I'm sure there must be a nice way of trimming the nonoseconds if you don't want them on the output.
Thanks,
Dan
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, October 23, 2009 7:48 AM
Points: 1,
Visits: 12
|
|
Great article. Glad to see there are separate date and time types.
I hope there will be more Is% functions. I hate to test a value with IsDate then get an error when trying to convert it to smalldatetime. There should be an IsDateTime2, IsDateTime, IsSmallDateTime, IsDate, IsTime, etc.
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Friday, June 12, 2009 2:32 AM
Points: 822,
Visits: 60
|
|
Good summary. I still want to know what happens if I request the number of nanoseconds between Year 0001 and 9999 using datediff. Cos as far as I know datediff won't support a number as big as that.
I've probably got a few more questions that will become evident when I finally get round to digging into this.
-Jamie
Jamie Thomson
Conchango UK PLC
'Innovative Change In Business'
http://www.conchango.com
http://blogs.conchango.com/jamiethomson
|
|
|
|
