Problem converting in UPDATE
-
I am trying to take two fields from a table, both defined as varchar(132) and convert them to decimal(10,2). Then I want to add them together and convert them back to varchar(132) and update a third field. It looks okay when you do it in a SELECT statement but I get an error message doing the same in an UPDATE statement. Following is the UPDATE and SELECT code and the error message.
UPDATE db.dbo.T1 SET F3 = CONVERT(varchar(132), CONVERT(decimal(10,2), F1) + CONVERT(decimal(10,2), F2))
SELECT CONVERT(decimal(10,2),F1), CONVERT(decimal(10,2),F2),CONVERT(varchar(132), CONVERT(decimal(10,2),F1)+ CONVERT(decimal(10,2),F2)) FROM T1
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric
Any ideas or help would be appreciated.
-
Something like this works just fine for me:
create table #t
(
col1 varchar(123)
, col2 varchar(123)
, col3 varchar(123)
)
insert into #t (col2, col3) select '12.49', '2.51'
update #t set col1 = convert(varchar(123), convert(decimal(10,2),col2)+convert(varchar(123),col3))
select * from #t
drop table #t
Are you sure your underlying data is correct?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I actually got my data to work. I had to convert the two fields to money add them together and then convert them to decimal for the fomatting I wanted and convert the result to varchar to insert into the table.
Thanks,
-
Glad to hear that
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply