# 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●10●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 31.7k●323●465●680 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:

×218

question asked: 17 Mar '16, 02:45

question was seen: 400 times

last updated: 17 Mar '16, 05:20