Converting Text to Decimal

benchpolo

I have a set of data that has a value format of 0000014033. 0000014033 is a dollar value with 2 decimal places. I need to convert the 0000014033 to 140.33. Can someone assist me in writing the code?
Thanks.

ck9663

What do you have so far?

--- CK

benchpolo

I was going to do

declare
@value varchar(9)

set @value = '0001122'

select cast(@value as decimal(5,2)) as result

but i get this error
Server: Msg 8115, Level 16, State 8, Line 6
Arithmetic overflow error converting numeric to data type numeric.

ck9663

try

Expand|Select|Wrap|Line Numbers

  
declare

@value varchar(9)
 
set @value = '0001122'
 
select cast(@value as decimal(10,2))/100.00 as result

Handle the decimal place display on your front-end. Don't stress your server. If you insist of doing it, convert the entire expression to decimal(10,2) again.

Happy Coding!!!

--- CK

benchpolo

Actually I'm planning to create a VIEW bec the data that comes back from our vendor has it as 0001122, and our user needs to see it in dollar with two decimal places.
Thanks for helping me. =)

Converting Text to Decimal

re: Converting Text to Decimal

re: Converting Text to Decimal

re: Converting Text to Decimal

re: Converting Text to Decimal

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about