Search code examples
sqlsql-serversybase

Same SQL, different result in Sybase and SQL Server

The following query returns different values in Sybase and SQL Server.

select CONVERT(numeric(12, 6), round((-0.00193499999999991) / (30) * (9) + 9.742500000000000e-001, 6))

In Sybase it returns 0.973670 while in SQL Server it returns 0.973669. Do you know why and which one is the right result?

Changing the round precision from 6 to 7 causes Sybase to also return 0.973669 but I'm not sure why this is required.SQL Server also returns 0.973669 with round precision 7 

select CONVERT(numeric(12, 6), round((-0.00193499999999991) / (30) * (9) + 9.742500000000000e-001, 7))

Thanks

Resolved : Gordan's answer below pointed me in the right direction. I neglected to mention a detail. The literals in the query were actually values read as variables in a Stored Proc which was then used in the query I posted. The source of the literal values was the difference between two database fields declared as numeric(10,6). The legacy SP has two variables declared as float and the difference of 0.972315 and 0.974250 was producing -0.001935 however in SQL Server it was producing -0.00193499999999991 which is not surprising as its floating point math. So Sybase was masking the issue. Changing the SP variable to be of type numeric(12,6) resolved the issue and the results in Sybase and SQL Server match. I'll review the other SP code for use of variables of type float.

Solution

  • Based on my reading of the relevant documentation (Sybase and SQL Server), the two databases handle number literals differently.

    • Sybase interprets-0.00193499999999991 as a floating point number.
    • SQL Server interprets it as a decimal number.

    This probably explains the difference in the results.

    As for which is correct. I'm pretty confident in the arithmetic operations of various databases. I would say that both are correct, given the rules that they follow for interpreting the query and calculating the values.