SQL Server - Error converting data type nvarchar to bigint

Question

When I am executing the below query with SELECT * I am getting the following error :

[S0005][8114] Error converting data type nvarchar to bigint.

When I am executing this query only with SELECT id , ROW_NUMBER() ...  everything works.

My database looks like this:

This query runs well with the other table where the id column is NVARCHAR

The ID column is number only and if I cast it as CAST I get the same error.

Answer 1

Normally when I am getting this error, it is because there is whitespace on the front or end of the column. Here is how to 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 make sure that the ID is just the number only. I am also thinking that there aren't any alpha characters with the ID also check if small ids don't have leading zeros.

Looking for SQL Tutorial? Come and join the SQL server training course and certification to gain more knowledge on SQL.