CODEWITHSUNDEEP
sqlsql-servert-sql
totalfreakingnoob
totalfreakingnoob

Reputation: 423

SQL - Convert number to decimal

I'm trying to convert a number to a decimal with two decimals places.

SELECT CONVERT(DECIMAL(10,2),12345)

The above would return 12345.00 but I'm trying to achieve 123.45

Upvotes: 1

Views: 17887

Answers (3)

Allan
Allan

Reputation: 17429

Correction: The premise is somewhat flawed, as the data type of a literal number without a decimal point is int, not numeric as implied by the question. In that case, you do need to convert the initial value to either numeric or decimal before dividing:

SELECT CONVERT(DECIMAL,12345)/100

or

SELECT CAST(12345 AS DECIMAL)/100

(cast is the SQL standard, so if you ever want to apply this to other databases, it would be the preferred method.)

Alternately, you can just add a decimal point to the divisor, as SQL server will return the more precise data type when doing arithmetic on heterogeneous types:

SELECT 12345/100.0

According to the documentation, the numeric data type is functionally equivalent to the decimal datatype, so there's really no reason to convert between the two. It seems that all you really want to do is divide the value you have by 100:

SELECT 12345/100

Upvotes: -3

BBauer42
BBauer42

Reputation: 3657

SELECT CONVERT(DECIMAL(10,2),CAST(12345 as float)/CAST(100 as float))

Upvotes: 2

Thanos Markou
Thanos Markou

Reputation: 2623

You need something like that:

SELECT CONVERT(DECIMAL(15,2),12345/100.0)

Upvotes: 6

Related Questions