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

Fun with datetime Expand / Collapse
Author
Message
Posted Friday, November 23, 2007 1:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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, PhD
GoldenGate Software
Post #425184
Posted Friday, November 23, 2007 2:52 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: 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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, PhD
GoldenGate Software
Post #425223
Posted Friday, November 23, 2007 4:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #425229
Posted Friday, November 23, 2007 5:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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, PhD
GoldenGate Software
Post #425234
Posted Friday, November 23, 2007 4:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #425365
Posted Saturday, November 24, 2007 2:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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

Add to briefcase

Permissions Expand / Collapse