SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with datetime


Fun with datetime

Author
Message
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 3889
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
Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 1585
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


Smile

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 Smile

Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Piotr.Rodak
Piotr.Rodak
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2664 Visits: 1761
Thanks Andras, that was excellent.

...and your only reply is slàinte mhath
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 3889
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
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 1585
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 Smile)
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 Smile 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
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 3889
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
Jeff Moden
SSC Guru
SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)

Group: General Forum Members
Points: 203396 Visits: 41949
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 1585
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 Smile) (and then we have the nice new date and time, and datetime2 Smile in 2008 Smile)

Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)

Group: General Forum Members
Points: 203396 Visits: 41949
So long as they don't change it to decimal math... that would just slow it down BigGrin

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 3889
Thanks for your valuable inputs guys :-)

Best Regards,

Chris Büttner
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search