Sql-server – Will SQL Server “int” datatype reliably truncate (and not round) decimal values when they are input

integersql server

I have a user with software that sometimes sends back a UNIT's ID as an integer(ex. 1000), and sometimes with a small decimal value appended (ex. 1000.001). The software automatically generates MSSQL (2016 SP1) tables with "real" datatypes, and to "top it off" makes this UNIT columns part of a Primary Key. This causes issues on the data input side and the reporting side.

I realize this is really a "bug" with the software, but I'm trying to be helpful on the DB side. I have proven in a Sandbox region that I can change the "real" datatype to an "int", that this does not cause data loss with existing data, and every input scenario shows that the decimal values of any input will be truncated in the "int" column(not rounded).

My questions are, "Will SQL Server always reliably truncate decimal values when input into an "int" datatype (We do not have a way to dig into the Packaged App and force it to ROUND)? Are there any scenarios where this will error, or cause issues?" I can't find this documentation anywhere.

Best Answer

The Microsoft Docs page for the float and real data types, says this:

Values of float are truncated when they are converted to any integer type.

In addition the CAST and CONVERT (Transact-SQL) page says:

When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.

conversion table

I'd prefer to get the source software modified to not return floating point data, but short of that, if the data is not used by some process that would be life-threatening, then it looks like an implicit conversion to int should be safe.

I qualified it with "non-life-threatening" because it's possible there is some situation where the implicit conversion does not operate the way it is documented. Take for instance, the details presented in the "Caution" section of the int Docs page.

Related Question