Skip to Content
Technical Articles
Author's profile photo Dorothy Eiserman

HANA SQL: Casting to decimal (implicit or explicit) will not provide rounding

This is going to be a short blog but will hopefully highlight accuracy problems that might be occurring if you don’t explicitly round your formula results before:

  • explicitly casting them to decimal using to_decimal, or
  • returning them as decimal from a procedure or table function i.e implicit casting based on definition of return table/fields

I picked this up whilst doing a very detailed test script and noticing that I was losing cents everywhere, after a bit of investigation I realised a truncate was being performed instead of proper rounding. Small differences on each amount but a lot of small differences can in the end make a huge difference.

Note: TO_DECIMAL used to provide implicit rounding but this behaviour changed from SP06 onwards, please refer to SAP Note 1895981 – to_decimal no longer provides rounding.

Here is my sample code you can paste into SQL console to play with the behaviour:

DO BEGIN

DECLARE dec_value DECIMAL(23,2) := 10407.06;

-- Take dec_value and scale by 1000
SELECT dec_value/1000 AS "ResultNotRounded" FROM dummy;

-- Then cast it to DECIMAL(23,2) to simulate the cast that happens when returned by table function
SELECT to_decimal(dec_value/1000,23,2) AS "ResultCastToDecimal" FROM dummy;

-- To get the properly rounded answer, round first then cast
SELECT to_decimal(round(dec_value/1000,2),23,2) AS "ResultRoundThenCast" FROM dummy;

END;

Here we have the result of the formula to 5 decimal places:

Here is the result of the formula cast to 2 decimal places, you can see here it that it simply performed a truncate instead of rounding to 2 decimal places:

Here you can see that rounding it to two decimal places and then casting preserves the correct rounded value:

Hope this small tip helps someone achieve more accurate results. Have a great day!

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dot Eiserman
      Dot Eiserman

      I’ve consolidated all my profiles into central profile, if you have a comment/question, please don’t comment on this old blog, please go to the following repost or send me a message:

      https://blogs.sap.com/2019/09/25/hana-sql-casting-to-decimal-implicit-or-explicit-will-not-provide-rounding-2/