Skip to main content

I am trying to do math in my sql commands and would like to always get back 2 decimal place results that are rounded.  In regular SQL I can do a cast(a * b, as money) and that will give me what I need. I have tried using convert(a * b, sql_decimal) but this will always return the number of decimal places based on the result of what a * b is. How can I force rounded 2 decimal places? I have tried to use sql_float but to no avail.


#relativityrounding

I am trying to do math in my sql commands and would like to always get back 2 decimal place results that are rounded.  In regular SQL I can do a cast(a * b, as money) and that will give me what I need. I have tried using convert(a * b, sql_decimal) but this will always return the number of decimal places based on the result of what a * b is. How can I force rounded 2 decimal places? I have tried to use sql_float but to no avail.


#relativityrounding

Cliff,

You say, "In regular SQL..."

There are proprietary extensions/dialects in some products that have this feature.  However, there is no definition of "regular SQL" and standard SQL, as defined by ISO, does not have this feature.

I think you are placing the expectation of rounding and/or truncation on the database when it would seem to me to more correctly belong in the user interface.  Given the large number of rounding algorithms, the expectation for rounding is too cultural- and industry-specific.  The database should store the data; the user interface should manipulate the data for presentation.

Microsoft is the specifier of the SQL language, including scalar functions, supported by ODBC.  Money is not a data type in the specification, nor does the CONVERT scalar function (which is in the specification)  support rounding.

Terms & ConditionsAccessibility statement