Difference when casting a datetime to a decimal on

Question

On two different sql servers I run the following query:

 declare @myDatetime as datetime = '2017-07-04 23:42:32.400'
 select CAST(@myDatetime AS DECIMAL(20,5))

I get two different results: 42918.98788 and 42918.98787

If I cast to a DECIMAL(20,6) it works fine (42918.987875) but let's say I need to put it in a decimal(20,5).

Where can I found the source of this difference in behaviour when rounding? Is it an option somewhere that rounds the final 5 up or down? Is it a sort of locale, international setting, collation or else? Is it the different verions of SQL (12.0.5000.0 vs 13.0.4202.2)?

Answer 1

According to this document: https://support.microsoft.com/en-us/help/4010261/sql-server-2016-improvements-in-handling-some-data-types-and-uncommon

Microsoft have made some changes to how it handles some "uncommon" conversions:

SQL Server 2016 includes improvements to the precision of the following operations under compatibility level 130:

    Uncommon data type conversions. These include the following:
        float/integer to/from datetime/smalldatetime
        real/float to/from numeric/money/smallmoney
        float to real

So I would suspect that might be what we see for the different rounding, in this specific situation.