The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

# Cast to DECIMAL with a variable scale

 Here is a simple example: ``````BEGIN DECLARE three INTEGER; SET three = 3; -- Working SELECT CAST(32.3369 as DECIMAL(30, 3)); -- Not working --SELECT CAST(32.3369 as DECIMAL(30, three)); END`````` Is there a way to cast a decimal to a variable scale? Business case: Some currency amounts are rounded to 3 decimals in some cultures, others to 2. asked 17 Mar '16, 02:45 tzup 360●8●13●24 accept rate: 0%

 ``````BEGIN DECLARE three INTEGER; SET three = 3; -- Working SELECT CAST(32.3369 as DECIMAL(30, 3)); -- Working :) SELECT Round(32.3369, three), Truncnum(32.3369, three) ; END`````` answered 17 Mar '16, 04:01 Dmitri 1.5k●4●11●32 accept rate: 11% Great! Thank you. (17 Mar '16, 05:19) tzup
 EXECUTE IMMEDIATE is your friend here if you want to use CAST with a DECIMAL and a variable scale. ```BEGIN DECLARE three INTEGER; SET three = 3; -- Working SELECT CAST(32.3369 as DECIMAL(30, 3)); -- Now working EXECUTE IMMEDIATE 'SELECT CAST(32.3369 as DECIMAL(30, ' || three || '))'; END ``` For ROUND(), Dmitri's approach is strongly recommended... answered 17 Mar '16, 03:58 Volker Barth 29.3k●287●438●644 accept rate: 32% Good tip on using EXECUTE IMMEDIATE. Thanks! (17 Mar '16, 05:20) tzup
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×186

question asked: 17 Mar '16, 02:45

question was seen: 221 times

last updated: 17 Mar '16, 05:20