CODEWITHSUNDEEP
c#sql-server
Frank Gao
Frank Gao

Reputation: 31

How to cast a column to decimal type in SQL Server?

tablesAA has two columns, amount is decimal (28,10), amountscale is int.

I need to cast the amount to decimal [ (p[ ,s] )].

The 's' is dynamic from the amountscale column value, but the bottom code causes a syntax error.

So, how to do cast of amount depending on the amountscale value?

select 
    amount, amountscale,
    cast(amount as decimal(28, amountscanle)) as amount 
from tablesAA

Upvotes: 0

Views: 132

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Alas, for a cast(), the scale and precision need to be constants.

If you just want the data output with a particular scale and precision, then you can convert them to a string using str() (documented here):

select amount, amountscanle,
       str(amount, 28, amountscanle) as amount2
from tablesAA;

An alternative is the brute force approach:

select amount, amountscanle,
       (case when amountscanle = 0 then cast(amount as decimal(28, 0)
             when amountscanle = 1 then cast(amount as decimal(28, 1)
             when amountscanle = 2 then cast(amount as decimal(28, 2)
             . . . 
        end) as amount2
from tablesAA;

Upvotes: 2

Related Questions