Error Converting Data Type nvarchar to binint

I have a query that failing with this error:

"Error converting data type nvarchar to bigint"

My line that's failing is this:
INNER JOIN Country ON ColMeta.xCountry = Country.countryID

ColMeta.xCountry is nvarchar(1000)
Country.countryID = bigint

Is there a way to convert this on the fly (here in the query) without having to modify the database tables?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Is colMeta.xcountry the NAME of the country?  If so, perhaps your country table contains a NAME as well and you could join on the name instead of the country.countryID


INNER JOIN Country ON ColMeta.xCountry = Country.countryNAME
Kevin CrossChief Technology OfficerCommented:
What are the values of ColMeta.xCountry?

Think issue is most likely the field size if your data consists of all numeric/whole number values and no extraneous text.

You can try a cast, but may have to do some trimming first.
Nathan RileyFounderCommented:
inner join country on colmeta.xcountry = cast(country.countryid as nvarchar)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Kevin CrossChief Technology OfficerCommented:
I would agree with Brandon as I double checked and size doesn't impact this with NVARCHAR.  I have seen it with some datatypes that it needs a numeric data type with same length, but I was able to cast NVARCHAR(1000) as long as the length of actual data strings are 19 characters or less.

If they are over that, then you have to use a different data type.

If you have string values, then find different column to match against like Brandon indicated.
Kevin CrossChief Technology OfficerCommented:
LOL, that will work too...
Kevin CrossChief Technology OfficerCommented:
Sometimes it is the simpliest ways that work best.  Gallitin's solution to focus on casting the BIGINT to NVARCHAR(1000) is a good approach.  Although the fact that the xcountry would not cast to BIGINT would state that it will probably not match up to your BIGINT countryid for at least one value which may be what you want anyway since you are using an INNER JOIN.

Nathan RileyFounderCommented:
Yeah don't know if it would work, but figured it was a place to start.  When I first got on here cast and convert were the hardest thing for me, but know I have a whole knowledge base from everyone helping me:)
greddinAuthor Commented:
ColMeta.xCountry is just a numeric value. Thanks.
greddinAuthor Commented:
Thanks to all who responded. But this is exactly what I was looking for.
Kevin CrossChief Technology OfficerCommented:
Greddin, glad Gallitin's solution worked for.  To solve the root of the issue though, I would check your data for xcountry value greater than 19 characters if you can't change datatype.  Spaces don't impact the cast, so it is actual data length.

SELECT xCountry
FROM ColMeta
WHERE LEN(LTrim(RTrim(xCountry))) > 19

Good luck!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.