CODEWITHSUNDEEP
sqlsql-server
J.J.
J.J.

Reputation: 1118

Why is this SQL convert to bigint not working?

I am inner joining a few SQL Server tables, and there is a varchar column that needs to match up with a numeric column. I am attempting to convert that column to bigint, but it will come up with

Error converting data type varchar to bigint

when attempting to do so. Can anyone point me in the right direction on how to fix this? Thanks!

SELECT 
    userid, COUNT(DISTINCT maintlist.maintid) as thecount 
FROM 
    Userlist 
INNER JOIN 
    maintlist ON CONVERT(bigint, MaintList.enteredby) = userid 
INNER JOIN
    maintnotes ON maintnotes.maintid = maintlist.maintid 
WHERE
    deptnum <> '0' 
    AND notedate BETWEEN '2015-10-01' AND '11/1/2015' 
GROUP BY 
    userid 
ORDER BY  
    thecount DESC;

Upvotes: 0

Views: 2470

Answers (2)

Robert van den Berg
Robert van den Berg

Reputation: 106

If some user IDs in the maintlist table can't be converted from varchar to bigint, converting the user ID in the userlist table to varchar means that your code will work, but your join will not give the results you want. So I'd still go looking for the user IDs that can't be converted.

Upvotes: 1

J.J.
J.J.

Reputation: 1118

Thanks for the help, I was able to convert the userid to varchar instead.

SELECT userid, COUNT(DISTINCT maintlist.maintid) as thecount from Userlist inner join maintlist ON MaintList.enteredby = convert(varchar(20),userid) inner join maintnotes on maintnotes.maintid = maintlist.maintid where deptnum <> '0' AND notedate between '2015-10-01' AND '11/1/2015' group by userid order by thecount DESC;

Upvotes: 0

Related Questions