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 Wednesday, October 24, 2007 8:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 266, Visits: 2,566
Datetime2? I agree. Yuck. Why not BigDatetime? We have SmallDatetime...
Post #414454
Posted Wednesday, October 24, 2007 8:31 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
JJ B (10/24/2007)
Datetime2? I agree. Yuck. Why not BigDatetime? We have SmallDatetime...


tinyint, smallint, int, bigint
smalldatetime, datetime, bigdatetime

The simplicity of your suggestion is so elegant it hurts.

Silly Microsoft!!!

Expect a synonymn in SQL Server 201X for datetime2!

-Jamie



Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Post #414462
Posted Wednesday, October 24, 2007 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 7,115, Visits: 14,983
Jamie Thomson (10/24/2007)
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.


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.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #414519
Posted Wednesday, October 24, 2007 11:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:33 AM
Points: 1,358, Visits: 380
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.
Post #414539
Posted Wednesday, October 24, 2007 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 26, 2009 9:00 AM
Points: 2, Visits: 31
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
Post #414647
Posted Thursday, October 25, 2007 8:16 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, November 15, 2013 9:43 AM
Points: 183, Visits: 235
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)?
Post #414981
Posted Thursday, October 25, 2007 8:34 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
Joe Barbian (10/25/2007)
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)?


It seems like a bad idea (to me anyway) to change the behaviour of an existing datatype. hardly useful for backwards compatability is it?


Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Post #414992
Posted Thursday, October 25, 2007 7:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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
print cast(getDate() as float) -- this returned 39379.5 despite the time currently being 2007-10-26 11:19am
print cast(39379.5 as datetime) --this returned 2007-10-26 12:00pm

Correct 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 print cast(0 as datetime) 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 :D

Anyhow, 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!



Post #415174
Posted Thursday, October 25, 2007 7:59 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
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...


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

http://www.karaszi.com/sqlserver/info_datetime.asp

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.


-- JP
Post #415177
Posted Friday, October 26, 2007 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 5,228, Visits: 9,443
Jonathon Prosper (10/25/2007)
[quote]If I want the date to say 1/1/0000 I should have some option to do so.


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
Post #415257
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse