Sql Server - Error Converting Data Type Nvarchar to Bigint
When I Run Following Query with Select * I Get Error Saying: [S0005][8114] Error Converting Data Type Nvarchar to Bigint. Select * from ( Select *...
When I run following query with SELECT * I get error saying :
[S0005][8114] Error converting data type nvarchar to bigint.
SELECT * FROM (
SELECT * , ROW_NUMBER() OVER (ORDER BY CAST(id as BIGINT)) AS RowNum
FROM users
) AS users
WHERE users.RowNum BETWEEN 0 AND 5 ;
When I run this query only with SELECT id , ROW_NUMBER() ... everything works.
My DB looks like this:
This query run well with other table where id column is NVARCHAR
ID column is number only and if i cast it as : CAST(id as NVARCHAR) i get same error.
EDIT:
I Found problem with column ID values
ID 46903836 ID 9100000004
Small ids dont have leading zeros
3 Answers
Usually when I get this error it is because there is whitespace on the front or end of the column. Here is how I fix it.
SELECT * FROM (
SELECT * , ROW_NUMBER() OVER (ORDER BY CAST(LTRIM(RTRIM(id)) as BIGINT)) AS RowNum
FROM users
) AS users
WHERE users.RowNum BETWEEN 0 AND 5 ;
This will ensure ID is just the number only I am also assuming that there aren't any alpha characters with the ID.
You Don't need to cast your id column as it is already in bigint datatype
Your ID field is BIGINT (you have posted your table structure), this don't cause the error in your question.
But, because is unuseful the CAST you can rewrite your query as follow:
SELECT * FROM (
SELECT * , ROW_NUMBER() OVER (ORDER BY id) AS RowNum
FROM users
) AS users
WHERE users.RowNum BETWEEN 0 AND 5 ;
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
