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

5

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.

6

You Don't need to cast your id column as it is already in bigint datatype

1

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 ;

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy