Inserting varchar to Decimal/Numeric

Hi,

Can we insert varchar values to Decimal(9,2)/numeric(9,2) fields?

I've a temp table with varchar values. I checked using isnumeric and all the values are numeric. But when I do the insert, it fails.

I was able to insert the following values:

000290165
000501075
000290165
000326314

But NOT the following:

010773474

All the values are 9 digits only.......

How can I convert this kind of numbers?

I need to convert all the 9 digit numbers to XXXXXXX.xx format.

(7digits.2 digits)

Thanks,

Siva.

I believe that in this case your expectations are incorrect. When I run:

Code Snippet

select inpt,
convert(numeric(9,2), inpt)
as convertedValue
from ( select '000290165' as inpt union all
select '000501075' union all
select '000290165' union all
select '000326314' union all
select '010773474'
) a

/*
inpt convertedValue
--
000290165 290165.00
000501075 501075.00
000290165 290165.00
000326314 326314.00

(5 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
*/

The output is as expected. But I don't think that is what you are expecting. To get this correct try:

Code Snippet

select inpt,
convert(numeric(9,2), left(inpt,7) + '.' + right(inpt,2))
as convertedValue
from ( select '000290165' as inpt union all
select '000501075' union all
select '000290165' union all
select '000326314' union all
select '010773474'
) a

/*
inpt convertedValue
--
000290165 2901.65
000501075 5010.75
000290165 2901.65
000326314 3263.14
010773474 107734.74
*/

|||

Wow!! Great!

It worked like a magic!!

Thanks much...

Siva.