SQLServerCentral / Discuss Content Posted by Vincent Rainardi / Article Discussions / Article Discussions by Author / Date and Time in SQL Server 2008 / Latest Posts

RE: Date and Time in SQL Server 2008

Matt Miller (#4) — Sun, 04 Nov 2007 20:57:48 GMT

[quote][b]Wayne West (11/1/2007)[/b][hr]I understand Novell doesn't have this problem, apparently they serialize their time so the clock knows that it's off in regards to jobs and it either speeds up or slows down slightly until it adjusts back to the correct time.I'm glad that I don't work with Novell. :D[/quote]Ah - but synthetic time can be VERY useful, since it allows you to keep everything sequenced appropriately.... What if you screwed up the server's date and time by, say - one full day when you set it up? And you mess up while you are traying to get the right time back into play....After all - this wouldn't be the first technology that got "ported" or "adopted", or "renamed" (however you like to characterize features designed by Novell somehow appearing with new names and largely the same functionality in MS products....) Hmm let's see if we can remember a few...NDS (ADS) or ZenWorks (Microsoft SMS server)...:cool:

RE: Date and Time in SQL Server 2008

Vincent Rainardi-266504 — Sun, 04 Nov 2007 10:04:25 GMT

Hi Jonathon, as mentioned on KB 931975, the DST issue still happens in SQL 2005, i.e. today (4th Nov) at 2 am the scheduled jobs were not running for 1 hour because the clock was wound back to 1 am so SQL job agent thought that the jobs had already been executed. Adding extra scheduled jobs as Wayne mentioned seems to be a good work around; thanks Wayne. I am not sure if this SQL Agent behavior is fixed SQL 2008 or not. None of the date and time data type in SQL 2008 is DST aware. The datetimeoffset is time zone aware, but it is not DST aware. Perhaps this is because SQL Server gets the time information from Windows API.Kind regards,Vincent

RE: Date and Time in SQL Server 2008

Wayne West — Thu, 01 Nov 2007 17:20:50 GMT

I understand Novell doesn't have this problem, apparently they serialize their time so the clock knows that it's off in regards to jobs and it either speeds up or slows down slightly until it adjusts back to the correct time.I'm glad that I don't work with Novell. :D

RE: Date and Time in SQL Server 2008

JP-317675 — Thu, 01 Nov 2007 17:17:59 GMT

Thanks Wayne. Lets hope that 2008 deals with this issue without the need for these sorts of workarounds.

RE: Date and Time in SQL Server 2008

Wayne West — Thu, 01 Nov 2007 17:05:41 GMT

[quote][b]Jonathon Prosper (10/28/2007)[/b]Another question though, we have had some issues lately regarding jobs and daylight savings. The biggest issue has been the job scheduler not being able to tell the difference between a local time before daylight savings and the local time after daylight savings. This means that for jobs executing on a schedule frequency of less than one hour the jobs are ignored between 2am and 3am AFTER daylight savings takes effect as it believes the jobs have already been executed.Does anyone know if the job scheduler will implement any of these new datetime datatypes specifically the datetime offset in 2008? I'm an unsure whether this issue was already resolved in 2005.[/quote]I can't address the 2008 side of it specifically, but I saw a simple workaround. Let's say you have a job that runs at 15 minute intervals all day long. Schedule it to run from 0200-0100, then add three more schedule steps to run at 0115, 0130, and 0145. When the system clock backs up due to DST, you're still covered.

RE: Date and Time in SQL Server 2008

JP-317675 — Sun, 28 Oct 2007 19:17:31 GMT

[quote]Jonathon Prosper, thanks for your explanation regarding Tibor Karaszi explanation about why 1753 is the earliest date for datetime. I found it very useful.[/quote]No problem!Another question though, we have had some issues lately regarding jobs and daylight savings. The biggest issue has been the job scheduler not being able to tell the difference between a local time before daylight savings and the local time after daylight savings. This means that for jobs executing on a schedule frequency of less than one hour the jobs are ignored between 2am and 3am AFTER daylight savings takes effect as it believes the jobs have already been executed.Does anyone know if the job scheduler will implement any of these new datetime datatypes specifically the datetime offset in 2008? I'm an unsure whether this issue was already resolved in 2005.

RE: Date and Time in SQL Server 2008

Vincent Rainardi-266504 — Fri, 26 Oct 2007 16:23:27 GMT

Dear all, thank you for all your responses.Jonathon Prosper: I'm not sure how datetimeoffset is stored Jonathon, but I'm guessing that 6-8 bytes are used to store the date and time elements (YYYY-MM-DD hh:mm:ss[.nnnnnnn]), whilst 2 bytes are used to store the timezone ([+|-]hh:mm). Whether it take 6, 7 or 8 bytes depends on the precision of the fractional second element (the .nnnnnnn), i.e. if the precision is 0-2 decimal points it is 6 bytes, if the precision is 3-4 decimal points it is 7 bytes and if the precision is 5-7 decimal points it is 8 bytes.When a variable of datetimeoffset data type is implicitly converted to datetime data type, the result is the date and time components of the local datetime. For example, if the datetimeoffset is 2007-10-26 14:54:23.1234567 -06:00 then the datetime is 2007-10-26 20:54:23.123.For explicit conversion we have the option of specifying the style. Style 0 means we want the output in local datetime (in the above example the output is 2007-10-26 20:54:23.123) whereas style 1 means that we want the output in UTC (in the above example the output is 2007-10-26 14:54:23.123)Michel Stainer: Yes Michel you are right, it should be 31st December 9999. Thank you for correcting it.BanzaiSi: Yes you are right BanzaiSi, the name of the function is CURRENT_TIMESTAMP, not CURRENT_DATETIME. Thank you for highlighting this mistake.John Nolan: Please see below regarding datetime2 naming.Nebojsa Ilic: Thank you for posting the storage size. See below regarding datetime2 naming.Daniel Wolford: all the existing styles arguments for CONVERT function (0 to 131) work with the new data types. For example, to trim the nanoseconds part, we can do "convert (varchar, TIME, 108)" to produce hh:mm:ss or "convert (varchar, DATETIME2, 120)" to produce YYYY-MM-DD hh:mm:ss.rswinehart: It seems that in 2008 ISDATE() would still be the only function that determines whether a variable is a valid date or time value. I agree that ISSMALLDATETIME---------------------------------------------------------------------------John Nolan, Nebojsa Illic, JJ B, Jamie Thompson, Joe Barbian and Ian Yates regarding DATETIME2 NAMING: When I heard DATETIME2 I was also immediately thinking about Oracle's VARCHAR2. So I agree with you all that it's not a good name. I also agree that BIGDATETIME seems to be a more suitable name, as we hae SMALLDATETIME, SMALLINT, INT and BIGINT, as Jamie Thomson correctly mentioned.On 21/8/2007 Eland Somarskog mentioned on Microsoft Connect (http://connect.microsoft.com) that datetime2 was not a very good name for a data type, for example when we needed to say "datetime2(4)". He mentioned BIGDATETIME and NEWDATETIME but prefers DATEANDTIME. The reasoning was BIGDATETIME is misleading as DATETIME2 may be smaller than DATETIME, and NEWDATETIME will not be new in 2020.Microsoft's response was:"The naming decision we made was mainly based on:- demonstrating the transparency of the existing DATETIME type - ensuring the new type name can be general enough to represent all new enhancements Except for TIMESTAMP (ANSI SQL standard name for datetime), I should say we don’t see there is perfect name in the world about it. Unfortunately, TIMESTAMP is already taken for something else in SQL Server. After giving all the considerations, we believe that DATETIME2 is the best name against our guideline."I think Jamie and Eland is right that hopefully we will have an alias for DATETIME2. ----------------------------------------------------------------------------Jonathon Prosper, thanks for your explanation regarding Tibor Karaszi explanation about why 1753 is the earliest date for datetime. I found it very useful.http://www.karaszi.com/sqlserver/info_datetime.asp#Why1753Kind regards,Vincent Rainardi

RE: Date and Time in SQL Server 2008

BanzaiSi — Fri, 26 Oct 2007 01:57:22 GMT

Don't forget, if you run up against the limitations of the built-in types, you have the option of creating your own user-defined types.Unfortunately, it does require CLR integration to be turned on and there is a performance hit.See SQL Server 2005 Books Online"CLR User-Defined Types"Paste into IE (SQL2005 BOL installed):[url]ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/27c4889b-c543-47a8-a630-ad06804f92df.htm[/url](online version)[url]http://msdn2.microsoft.com/en-us/library/ms131120.aspx[/url]

RE: Date and Time in SQL Server 2008

John Mitchell-245523 — Fri, 26 Oct 2007 01:33:45 GMT

[quote][b]Jonathon Prosper (10/25/2007)[/b][hr][quote]If I want the date to say 1/1/0000 I should have some option to do so.[/quote]I think it might get a bit messy around the interface between BC and AD dates. This is because there was no year zero and therefore normal rules of arithmetic wouldn't apply.John

RE: Date and Time in SQL Server 2008

JP-317675 — Thu, 25 Oct 2007 19:59:13 GMT

[quote]Finally, if the zero date is the start of 1900 and the dates can go up to the year 9999, why can they not go back as far using negative floats? Going back <200 years whilst being able to go forward several thousand years is a bit odd...[/quote]Tibor Karaszi touches on this subject in the book Inside SQL Server 2005 :T-SQL Programming.In 1752 many countries that were still using the julian calendar switched over to the Gregorian calendar. If you allow dates prior to 1753 then you need to know which country you are talking about.The following link from the book gives a more detailed explanation [url]http://www.karaszi.com/sqlserver/info_datetime.asp[/url]My opinion is that restrictions are useful in many applications but there needs to be the capacity to remove them. There is nothing worse that having a datatype waving its finger at you saying "mmm..hmmm...I don't think so...". If I want the date to say 1/1/0000 I should have some option to do so. If I want to store a time as 54:34:24.567 i would like to have the option to do so. I appreciate though that having these options may have some design challenges for the system based date and time functions.As an example a time datatype that is not limited to 24 hours would be very handy in certain applications. To store a duration currently it is best to store a numeric value representing minutes, seconds hours etc and use the dateadd function to add this duration on top of your start time. Formatting this numeric into the format 'hh:mm:ss' in result sets is a bit of a mess. A system datatype that could store this duration as 'hh:mm:ss.sss' without the hourly limit could alleviate some of these complications from a development perspective although may complicate things from sql server architecture perspective.

RE: Date and Time in SQL Server 2008

Ian Yates — Thu, 25 Oct 2007 19:25:33 GMT

I agree, datetime2 is very oracle-ish and BigDateTime is disgustingly obvious :) They cannot play with datetime as it (historically) has been stored as a float, much the same way many programming languages deal with datetime values. From what I've seen, they all seem to share a common zero date so that the value 39379.5 represents midday on 26th October 2007 (although I've never quite trusted this - see below).In QA, I did[font="Courier New"]print cast(getDate() as float)[/font] -- this returned 39379.5 despite the time currently being 2007-10-26 11:19am[font="Courier New"]print cast(39379.5 as datetime)[/font] --this returned 2007-10-26 12:00pmCorrect me if I'm wrong, but the article gave some time in the 1700's as the earliest date that could be represented in SQL Server. If I do [font="Courier New"]print cast(0 as datetime) [/font] I get "Jan 1 1900 12:00AM". However a zero datetime, when displayed in many software packages, comes up as 1899-30-12 - two days different (which is float -2 in SQL using the cast statement)... I've never seen a good explanation for this (anyone care to offer one? :D )Finally, if the zero date is the start of 1900 and the dates can go up to the year 9999, why can they not go back as far using negative floats? Going back <200 years whilst being able to go forward several thousand years is a bit odd... Perhaps my assumption about floats being behind the scenes is wrong? I haven't bothered checking BOL for this lately so if it was obvious in there please be nice :DAnyhow, it was a good introductory article. I was curious about the byte sizes of the types but someone's been kind enough to post them too!

RE: Date and Time in SQL Server 2008

Jamie Thomson — Thu, 25 Oct 2007 08:34:16 GMT

[quote][b]Joe Barbian (10/25/2007)[/b][hr]datetime2?why not just expand datetime to be more precise expecially if earlier comments are correct that it will take 8 bytes (i.e. no difference in storage space)?[/quote]It seems like a bad idea (to me anyway) to change the behaviour of an existing datatype. hardly useful for backwards compatability is it?

RE: Date and Time in SQL Server 2008

Joe B-478020 — Thu, 25 Oct 2007 08:16:13 GMT

datetime2?why not just expand datetime to be more precise expecially if earlier comments are correct that it will take 8 bytes (i.e. no difference in storage space)?

RE: Date and Time in SQL Server 2008

tthompson — Wed, 24 Oct 2007 15:05:00 GMT

Great article. Thanks for the insight. As a Reporting Services developer, I was hoping to find out that the TIME data type would be able to store values beyond 24 hrs. When developing charts in SSRS 2005, I noticed that it will not allow time as a data point in the y-axis (e.g. retrieval time or duration, that can last more than 24 hrs). I was hoping that would be resolved in 2008. :(The most frustrating aspect of this is that Excel can chart aggragated time in Excel with ease so it is difficult to explain why SSRS cannot when working with clients.Thanks,Todd

RE: Date and Time in SQL Server 2008

Mark D Powell — Wed, 24 Oct 2007 11:01:47 GMT

I think the only thing wrong with this is that the article could have used a few examples of the functions so you could see the result format.

RE: Date and Time in SQL Server 2008

Matt Miller (#4) — Wed, 24 Oct 2007 09:56:25 GMT

[quote][b]Jamie Thomson (10/24/2007)[/b][hr]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.[/quote]For what it's worth right now - Datetime2 seems to have a precision of 100 nanosecond only (meaning you won't be able to drill down to 1ns increments), so that should still fit into 8 bytes. Not sure how they're going to implement datediff increments to handle that.

RE: Date and Time in SQL Server 2008

Jamie Thomson — Wed, 24 Oct 2007 08:31:16 GMT

[quote][b]JJ B (10/24/2007)[/b][hr]Datetime2? I agree. Yuck. Why not BigDatetime? We have SmallDatetime...[/quote]tinyint, smallint, int, bigintsmalldatetime, datetime, bigdatetimeThe simplicity of your suggestion is so elegant it hurts.Silly Microsoft!!!Expect a synonymn in SQL Server 201X for datetime2!-Jamie

RE: Date and Time in SQL Server 2008

JJ B — Wed, 24 Oct 2007 08:18:19 GMT

Datetime2? I agree. Yuck. Why not BigDatetime? We have SmallDatetime...

RE: Date and Time in SQL Server 2008

Jamie Thomson — Wed, 24 Oct 2007 07:19:04 GMT

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

RE: Date and Time in SQL Server 2008

rswinehart — Wed, 24 Oct 2007 06:52:22 GMT

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.

RE: Date and Time in SQL Server 2008

Daniel Wolford — Wed, 24 Oct 2007 06:38:18 GMT

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 forConvert(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

RE: Date and Time in SQL Server 2008

Nebojsa Ilic — Wed, 24 Oct 2007 03:59:44 GMT

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 :)

RE: Date and Time in SQL Server 2008

Nebojsa Ilic — Wed, 24 Oct 2007 03:34:39 GMT

Storage size for new date and time data types are (in bytes):date 3time 3-5datetime2 6-8datetimeoffset 8-10

RE: Date and Time in SQL Server 2008

John Nolan — Wed, 24 Oct 2007 03:31:59 GMT

Yuk. Hope they change the name of data type DATETIME2. I just hate that.

RE: Date and Time in SQL Server 2008

BanzaiSi — Wed, 24 Oct 2007 03:00:48 GMT

HiFantastic 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 :-(

RE: Date and Time in SQL Server 2008

Michel Steiner — Tue, 23 Oct 2007 23:47:55 GMT

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?

RE: Date and Time in SQL Server 2008

JP-317675 — Tue, 23 Oct 2007 23:46:45 GMT

[quote]"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.[/quote]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.

Date and Time in SQL Server 2008

Vincent Rainardi-266504 — Mon, 17 Sep 2007 09:41:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/vRainardi/3253.asp