Error converting data type varchar to bigint.

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

General SQL Server Forums

New to SQL Server Programming

Error converting data type varchar to bigint.

Author

Topic

rekon32
Starting Member

16 Posts

Posted - 2013-08-09 : 13:39:02

What am I doing wrong?



select lhsvector,filterid,RHSValue1,convert(bigint,adjrules.RHSValue2) as R1, AdjValue from adjrules 
join adjvalues on adjvalues.RuleID = adjrules.RuleID
	where adjrules.ruleid in
		(
		select ruleid from planxadj where planid = 19494 
		)
	and adjrules.LHSVector = 'loanamt'
	and convert(bigint,adjrules.RHSValue2) < '417000'
order by adjrules.RHSValue2 DESC

Data Analyst

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-09 : 13:42:07

Is there data that isn't compatible with bigint?

convert(bigint,adjrules.RHSValue2) < 417000

How big is adjrules? This query will have performance issues.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rekon32
Starting Member

16 Posts

Posted - 2013-08-09 : 13:45:05

quote:
Originally posted by tkizer

Is there data that isn't compatible with bigint?

convert(bigint,adjrules.RHSValue2) < 417000

How big is adjrules? This query will have performance issues.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Adjrules has 23,516 rows. Not very big.

How can I tell if adjrules is compatible with bigint?

Data Analyst

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-09 : 13:48:00

You could start with: WHERE ISNUMERIC(adjrules)<> 1. That'll tell you if any values aren't numeric, though that would include decimal data too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rekon32
Starting Member

16 Posts

Posted - 2013-08-09 : 13:48:20

I just realized adjrules.RHSValue2 is not only number values.

Data Analyst

rekon32
Starting Member

16 Posts

Posted - 2013-08-09 : 13:51:54

quote:
Originally posted by tkizer

You could start with: WHERE ISNUMERIC(adjrules)<> 1. That'll tell you if any values aren't numeric, though that would include decimal data too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

It can be NULL or 'Not Specified'

Data Analyst

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-09 : 13:53:19

You need to exclude 'not specified' then.

RHSValue2 isn't the one throwing the error as you aren't converting it to bigint. Just adjrules is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rekon32
Starting Member

16 Posts

Posted - 2013-08-09 : 13:56:13

quote:
Originally posted by tkizer

You need to exclude 'not specified' then.

RHSValue2 isn't the one throwing the error as you aren't converting it to bigint. Just adjrules is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Excluding 'not specified' worked. Thank you!

Data Analyst

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-09 : 13:57:45

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources