CODEWITHSUNDEEP
sqlsql-server
Pat Doyle
Pat Doyle

Reputation: 384

Error converting data type nvarchar to bigint -when joining two differnet data types

I am trying to join two tables.

Table1.Column1 where column 1 is a BIGInt. On Table2.Column2 where column 2 is Nvarchar.

Here is what I am running 

Select HspId, CMSid, Cast(CMSId as nvarchar)
From Q2IMSSiteHistory2015old
inner Join HSP on HSP.CMSid = Q2IMSSiteHistory2015old.POS

I am getting the following Error: Error converting data type nvarchar to bigint.

Even if I do not cast anything I get the same error.

Upvotes: 0

Views: 16185

Answers (3)

CandleWax
CandleWax

Reputation: 2219

Cast the join.

Select HspId, CMSid, Cast(CMSId as nvarchar)
From Q2IMSSiteHistory2015old
inner Join HSP on CAST(HSP.CMSid as nvarchar) = Q2IMSSiteHistory2015old.POS

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

SQL Server tries to implicitly convert the nvarchar to bigint. You need to convert the bigint to nvarchar explicitly to avoid exceptions if your nvarchar column contains values that can't be converted to bigint:

Select HspId, CMSid, Cast(CMSId as nvarchar(20))
From Q2IMSSiteHistory2015old
inner Join HSP on Cast(HSP.CMSid as nvarchar(20)) = Q2IMSSiteHistory2015old.POS

Also, When you cast to any string value (char, varchar, nchar or nvarchar) you should always specify the max length of the string. SQL Server has a default length of 30, that should be enough in this case, but as best practice always specify length.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

First, always use a length with varchar in SQL Server.

Second, converting in the select has no effect on the on, so you need to convert twice.

Perhaps you intend:

Select HspId, CMSid, Cast(CMSId as nvarchar(255))
From Q2IMSSiteHistory2015old inner Join
     HSP
     on Cast(HSP.CMSId as nvarchar(255)) = Q2IMSSiteHistory2015old.POS;

Usually, I would recommend that you use try_convert(). Conversion to strings is pretty safe, though, so you can confident that it will work.

Upvotes: 0

Related Questions