Fun with datetime

Question

Fun with datetime

Christian Buettner-167247

SSChampion

Points: 13729
More actions
November 23, 2007 at 1:58 am

#181325

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

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

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 2

Thanks Andras, that was excellent.

...and your only reply is slàinte mhath

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

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

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 4

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

So long as they don't change it to decimal math... that would just slow it down 😀

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 9

Thanks for your valuable inputs guys 🙂

Best Regards,

Chris Büttner