Update Conversion Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Conversion Query

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-01-03 : 11:30:05

I have an update query that I need help with.

Updating a new source with an existing source but keep getting the error "Error converting data type varchar to numeric"

I am updating Employees decimal(18, 0) from a field in a different table Emps varchar (9)

Please can someone help me with a convert script?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-03 : 11:31:53

Use Convert() or Cast() function to convert the data from varchar to decimal.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-03 : 11:37:59

quote:
Originally posted by harsh_athalye

Use Convert() or Cast() function to convert the data from varchar to decimal.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

If the above doesnt work you may have non-numeric characters stored in the varchar column.

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-01-03 : 11:52:56

I have tried that field is set up as below:

EMPLOYEES_HERE__C = CONVERT(decimal(18, 0), B.EMPS_SITE),

Still getting the error! I have run a query to double check that B.EMPS_SITE and it only contains numerics?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 12:12:56

Try this:-
select convert(decimal(19,9), val) from table
where isnumeric(val) = 1

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-01-04 : 04:18:22

Sorry for any confusion but I am trying to do an update statement not a select :D

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-04 : 08:18:53

quote:
Originally posted by starnold

Sorry for any confusion but I am trying to do an update statement not a select :D

See what it returns

Select * from yourtable
where val like '%[a-zA-z]%'

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-04 : 08:22:26

quote:
Originally posted by starnold

Sorry for any confusion but I am trying to do an update statement not a select :D

I think there is some confusion from your side. The Select queries provided here are to cross check whether you have proper data in EMPS_SITE column in first place so that your UPDATE does not result in errors.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-01-07 : 05:13:59

I have tried that query and it returned zero results?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources