A Curious Case of NUMBER truncation

Oracle's FLOAT  may truncate a float number beyond its binary digit limit. For example, execute the following in Oracle:

DECLARE

testvar FLOAT(5);

BEGIN

Testvar := 123.45;

DBMS_OUTPUT.put_line(to_char(testvar));

END;

 

The statement will return 120 as the number 123.45 is rounded to 120, which has the max 4 binary digit.  This case above is documented here.

 

When you convert Oracle statement containing the FLOAT to SQL Server, SSMA converts Oracle float data type to SQL Server float data type as follows:

 

BEGIN

   DECLARE

      @testvar float(5)

   SET @Testvar = 123.45

   PRINT CAST(@testvar AS varchar(max))

END

 

However, SQL Server does not perform truncation and will preserve the value. The statement above will return 123.45.

 

Please note the differences in your migration project.