Conversion Failed - 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)

Conversion Failed

Author

Topic

sql_monkey
Starting Member

19 Posts

Posted - 2013-07-31 : 04:59:26

Hello,

Can anybody please tell me why this code:

UPDATE dbo.tblNotes SET Comments = 'Cancelled', Type = 'GC', EntryDate = '30-Jul-2013', 
	DateDue = '30-Jul-2013', DateMet = '30-Jul-2013', UserName = 'Steven', closedBy = 'Steven', RaisedBy = 'Steven'
FROM dbo.tblPolicy AS p
JOIN dbo.tblNotes AS n
ON p.PolicyID = n.ParentKey
WHERE p.PolicyID = '7132694'

Gives this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'MS/ABB' to data type int.

This is an insurance database and I am trying to add a note to a policy. I have checked the data types for each column and the date entries are set to datetime and the text entries are set to varchar so I can work out why I am getting a conversion failure.

Thanks
Steven

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 05:07:26

check the datatypes of PolicyID and ParentKey.
I think ParentKey is int and PolicyID is varchar, There are some values in PolicyID which are non numeric which is causing this error on the join condition I guess.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sql_monkey
Starting Member

19 Posts

Posted - 2013-07-31 : 05:42:34

Yes that's it but PolicyID is int and ParentKey is varchar.

How do I get round that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 05:46:35

need to add a filter on ParentKey to look only for numeric data

ie like


ParentKey NOT LIKE '%[^0-9]%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sql_monkey
Starting Member

19 Posts

Posted - 2013-07-31 : 06:32:23

Do I put that in the WHERE clause like this:

UPDATE dbo.tblNotes SET Comments = 'Cancelled', Type = 'GC', EntryDate = '30-Jul-2013', 
	DateDue = '30-Jul-2013', DateMet = '30-Jul-2013', UserName = 'Steven', closedBy = 'Steven', 
	RaisedBy = 'Steven'
FROM dbo.tblPolicy AS p
JOIN dbo.tblNotes AS n
ON p.PolicyID = n.ParentKey
WHERE p.PolicyID = '7132694' AND n.ParentKey NOT LIKE '%[^0-9]%'

Because that still gives the same error.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-31 : 06:55:27

You are doing an update in tblNotes for an explicit value (ParentKy = '7132694').
Can you explain why you are joining tblPolicy?

If that join is really needed then do the join by converting PolicyID to varchar for example like this:
ON convert(varchar(30),p.PolicyID) = ltrim(rtrim(n.ParentKey))

Best would be to correct your database model to NOT have different datatypes in such columns...

Too old to Rock'n'Roll too young to die.

sql_monkey
Starting Member

19 Posts

Posted - 2013-07-31 : 07:34:14

Thanks webfred that worked.

This query is just a test, I put the join in so that if in future I needed to add a comment to a batch of policies then I could use the batch number which appears in the policy table but not the notes table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 07:43:31

quote:
Originally posted by sql_monkey

Thanks webfred that worked.

This query is just a test, I put the join in so that if in future I needed to add a comment to a batch of policies then I could use the batch number which appears in the policy table but not the notes table.

I would definitely prompt you to do the change to data model as suggested by Fred. doing convertion on joins is not good idea

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sql_monkey
Starting Member

19 Posts

Posted - 2013-07-31 : 07:46:42

Where would you do the conversion if not on the join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 07:53:25

quote:
Originally posted by sql_monkey

Where would you do the conversion if not on the join?

The suggestion was to change the data model to make datatypes the same so that there's no need of conversion at all!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sql_monkey
Starting Member

19 Posts

Posted - 2013-07-31 : 08:12:01

OK I see but I cant do that, I dont administer the database, I'm only running reports.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 08:18:15

then no other way than to do the conversion.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sql_monkey
Starting Member

19 Posts

Posted - 2013-07-31 : 08:41:34

Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources