Ошибка при преобразовании типа данных nvarchar к bigint

user793468user793468

4,89823 gold badges81 silver badges126 bronze badges

I’m putting this as an answer, even though it doesn’t really answer the question, because I can’t fit this properly in a comment.

I ran this against SQL Server 2008 and I don’t get any errors..

BEGIN
  DECLARE @t TABLE(AsnNumber NVARCHAR(33))

  INSERT INTO @t (AsnNumber) VALUES('1777188')
  INSERT INTO @t (AsnNumber) VALUES('1777189')

  SELECT AsnNumber FROM @t

  SELECT CAST(AsnNumber AS BIGINT) as AsnNumber 
  FROM @t
  WHERE AsnNumber = '1777188';

  SELECT CAST(CAST(AsnNumber AS BIGINT) AS NVARCHAR(33)) as AsnNumber 
  FROM @t
  WHERE AsnNumber = '1777188';

  SELECT query1.*
  FROM (SELECT CAST(CAST(AsnNumber AS BIGINT) AS NVARCHAR(33)) as AsnNumber FROM @t) as query1
  WHERE AsnNumber = '1777188';

  WITH query1 (AsnNumber) AS 
  (SELECT CAST(CAST(AsnNumber AS BIGINT) AS NVARCHAR(33)) as AsnNumber FROM @t)
  SELECT AsnNumber FROM query1
  WHERE AsnNumber = '1777188';

END

This demonstrates that your query #1 works fine, and it also demonstrates that I can use it as a subquery and I can use it in a common table expression, and still no error. I have no idea why you’re getting an error.

Perhaps you can include a full set of statements to create the table, populate it with data, and then the exact query you’re running that produces the error? If you do that, it would help me to reproduce the issue, and then I could probably explain why it’s occurring. For now, I can’t reproduce your issue.

answered Aug 18, 2015 at 20:28

Lukáš IrsákLukáš Irsák

1,0921 gold badge14 silver badges23 bronze badges

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.

answered Dec 21, 2016 at 14:10

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 ;

answered Dec 21, 2016 at 14:12