Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Fun with datetime Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, November 23, 2007 1:58 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, October 7, 2014 2:56 AM Points: 2,842, Visits: 3,876
 Hello,Whats the difference between CAST(15.0000000385802490 AS datetime)and CAST(15.000000038580249 AS datetime) ?You guessed right, there is none. But what is the difference between CAST(16.0000000385802490 AS datetime)and CAST(16.000000038580249 AS datetime) ?Well, so far I can tell that the results are different, but I am now really curious to see the reason why.My first guess is that there is a float conversion somewhere in the middle, but it would be good if someone could confirm my blind guess here.Thanks! Best Regards, Chris Büttner
Post #425177
 Posted Friday, November 23, 2007 2:19 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, January 26, 2012 5:26 AM Points: 1,367, Visits: 1,585
 Hi Chris,SQL Server tries to guess the data type of your constants. For 16.0000000385802490 it guesses numeric(18,16), for 16.000000038580249 it guesses numeric(17,15). You could get back the same result if you do explicit casting:select CAST(16.0000000385802490 AS datetime), CAST(cast(16.000000038580249 as numeric(18,16)) AS datetime) I reckon when these data types are cast to float, there is a difference:select CAST(16.0000000385802490 AS float), CAST(16.000000038580249 AS float), CAST(cast(16.000000038580249 as numeric(18,16)) AS float) ---------------------- ---------------------- ----------------------16.0000000385803 16.0000000385802 16.0000000385803:)ps: you can explore the types of literals using the sql_variant_property:select sql_variant_property(16.0000000385802490,'BaseType' ), sql_variant_property(16.0000000385802490,'Precision ' ), sql_variant_property(16.0000000385802490,'Scale' )select sql_variant_property(16.000000038580249,'BaseType' ), sql_variant_property(16.000000038580249,'Precision' ), sql_variant_property(16.000000038580249,'Scale' )It is interesting I agree :)Regards, Andras Andras Belokosztolszki, MCPD, PhDGoldenGate Software
Post #425184
 Posted Friday, November 23, 2007 2:52 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, May 10, 2012 9:49 AM Points: 800, Visits: 1,759
 Thanks Andras, that was excellent. ...and your only reply is slàinte mhath
Post #425197
 Posted Friday, November 23, 2007 4:15 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, October 7, 2014 2:56 AM Points: 2,842, Visits: 3,876
 Hi Andras,thanks for your quick response.Can you imagine why there would be a float conversion at all?Thanks! Best Regards, Chris Büttner
Post #425218
 Posted Friday, November 23, 2007 4:25 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, January 26, 2012 5:26 AM Points: 1,367, Visits: 1,585
 Christian Buettner (11/23/2007)Hi Andras,thanks for your quick response.Can you imagine why there would be a float conversion at all?Thanks!Hi Chris,honestly I'm not sure if there is a float conversion at all (but that is a good hypothesis for now :))According to Books Online one should be able to cast numeric to datetime implicitly, so the cast may be an internal implementation detail.However, I'm wondering how frequently this would cause a problem :) Will you share with us the reason why you are casting those numbers to datetime? Regards, Andras Andras Belokosztolszki, MCPD, PhDGoldenGate Software
Post #425223
 Posted Friday, November 23, 2007 4:34 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, October 7, 2014 2:56 AM Points: 2,842, Visits: 3,876
 Sure I will:I looked at the QoD, which had the following statement that overflows.select datediff (ms,getdate()-28,getdate())Now when I read such a QOD, I usually play around in some directions to get used with it.So today I decided to identify the break point when it overflows through divide & conquer.So I added decimal places until I found the "break point". So there is no business problem around it Best Regards, Chris Büttner
Post #425227
 Posted Friday, November 23, 2007 4:47 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:19 PM Points: 35,769, Visits: 32,439
 DATETIME is really a FLOAT in the background (although it is a "fixed point float", if you can imagine that). DATETIME uses the same BINARY math as FLOAT. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #425229
 Posted Friday, November 23, 2007 5:00 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, January 26, 2012 5:26 AM Points: 1,367, Visits: 1,585
 Jeff Moden (11/23/2007)DATETIME is really a FLOAT in the background (although it is a "fixed point float", if you can imagine that). DATETIME uses the same BINARY math as FLOAT.You are right that datetime is some sort of a "mule". It takes 8 bytes, the first four is the number of 1/300 seconds since midnight, and the other 4 bytes are the days since 1/1/1900 (this can be negative, so it can express dates before 1900). But these could be calculated more precisely (were there a business case for it :)) (and then we have the nice new date and time, and datetime2 :) in 2008 :))Regards, Andras Andras Belokosztolszki, MCPD, PhDGoldenGate Software
Post #425234
 Posted Friday, November 23, 2007 4:08 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:19 PM Points: 35,769, Visits: 32,439
 So long as they don't change it to decimal math... that would just slow it down :D --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #425365
 Posted Saturday, November 24, 2007 2:40 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, October 7, 2014 2:56 AM Points: 2,842, Visits: 3,876
 Thanks for your valuable inputs guys Best Regards, Chris Büttner
Post #425451

 Permissions