By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Converting Text to Decimal

benchpolo
100+
P: 142
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.
Aug 4 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
What do you have so far?

--- CK
Aug 4 '09 #2

benchpolo
100+
P: 142
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.
Aug 4 '09 #3

ck9663
Expert 2.5K+
P: 2,878
try

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare
  3. @value varchar(9)
  4.  
  5. set @value = '0001122'
  6.  
  7. select cast(@value as decimal(10,2))/100.00 as result
  8.  
  9.  
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
Aug 4 '09 #4

benchpolo
100+
P: 142
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. =)
Aug 4 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.