Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Date and Time in SQL Server 2008 Expand / Collapse
Author
Message
Posted Monday, September 17, 2007 9:41 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/vRainardi/3253.asp
Post #399715
Posted Tuesday, October 23, 2007 11:46 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 8, 2010 5:04 AM
Points: 135, Visits: 90
"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
Post #414198
Posted Tuesday, October 23, 2007 11:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 29, 2014 2:10 AM
Points: 14, Visits: 46
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?
Post #414200
Posted Wednesday, October 24, 2007 3:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 28, 2013 4:13 AM
Points: 189, Visits: 34
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 :-(






Post #414265
Posted Wednesday, October 24, 2007 3:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 3, 2007 3:34 AM
Points: 99, Visits: 10
Yuk. Hope they change the name of data type DATETIME2. I just hate that.
Post #414282
Posted Wednesday, October 24, 2007 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 7:37 AM
Points: 163, Visits: 287
Storage size for new date and time data types are (in bytes):
date 3
time 3-5
datetime2 6-8
datetimeoffset 8-10
Post #414285
Posted Wednesday, October 24, 2007 3:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 7:37 AM
Points: 163, Visits: 287
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 :)
Post #414294
Posted Wednesday, October 24, 2007 6:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 9:44 AM
Points: 164, Visits: 72
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
Post #414381
Posted Wednesday, October 24, 2007 6:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:14 AM
Points: 7, Visits: 41
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.
Post #414394
Posted Wednesday, October 24, 2007 7:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, Visits: 188
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
http://sqlblog.com/blogs/jamie_thomson
Post #414412
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse