CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 13

Truncation When Casting Varchar to Decimal

I am trying to convert a varchar to a decimal in SSMS.

If I hard code a value, then the cast is successful. But if I try to use a variable, then the value is truncated. This also happens when using the convert function.

Why does this happen, and how can I use a variable to cast the varchar to it's correct decimal?

Example: 

Select  Cast('3.24' As Decimal(4, 3));
Result: 3.24


Declare @Number Varchar = '3.24';
Select  Cast(@Number As Decimal(4, 3));
Result: 3.00

Declare @Number Varchar = '3.24';
Select Convert(decimal(4,3), @Number)
Result: 3.00

Upvotes: 1

Views: 551

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

You need to give @Number a length. The default is 1, so it is only holding the first digit (3).

Try this:

Declare @Number Varchar(20) = '3.24';
Select  Cast(@Number As Decimal(4,2));
Result: 3.24

Upvotes: 9

Related Questions