Error converting data type nvarchar to bigint ошибка - Не ошибается лишь тот, кто ничего не делает!

Remove From My Forums

Question
Hi experts,

I execute the next code:

SELECT CAST(Order_Number AS bigint)
FROM Orders
WHERE Order_Number IS NOT NULL
AND ISNUMERIC(Order_Number) = 1
AND Order_Number NOT LIKE ‘%-%’
AND Order_Number NOT LIKE ‘%.%’

And besides having all those checks, it falis with: ‘Error converting data type nvarchar to bigint.’

How can I spot the row that is breaking this? The table has 1,000,000 rows, so I can’t look for it visually.

Thanks in advance!!!

Answers

Using NOT LIKE ‘%[^0-9]%’ should exclude any OrderNumber that has any character other than ‘0’ through ‘9’. Putting that in a CTE would certainly ensure that the NOT LIKE is true before trying the conversion, but it shouldn’t be trying the conversion
on a record that will not be selected. (If I know that I’m not going to select the record, why would I try to do work on it???)

I’m wondering, do you need to CONVERT(BIGINT, 2147483647)?? Might it be trying to convert the BIGINT into an INT to do the comparison?? (Just a WAG, but I’ve seen stranger things…)

HTH,

Carl
- Proposed as answer by
  
  Thursday, April 21, 2016 6:08 AM
- Marked as answer by
  Sam ZhaMicrosoft contingent staff
  Saturday, April 30, 2016 2:47 AM

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

asked Dec 21, 2016 at 14:05

Lukáš IrsákLukáš Irsák

1,0821 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

Wes PalmerWes Palmer

8804 silver badges15 bronze badges

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

S3S

24.8k5 gold badges25 silver badges44 bronze badges

answered Dec 21, 2016 at 14:13

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

Joe TarasJoe Taras

15.1k7 gold badges40 silver badges54 bronze badges

Источник

Remove From My Forums

Question
Hi: I am getting this data conversion error while I am trying to insert one table into another existing table (appending data). I would really appreciate any assistance on this please.

Thanks

Answers

Hi,

You should list the columns in your SELECT statement in order of the INSERT columns list. But here you haven’t inserted any column. Also you can use a SELECT INFO statement. Therefore a table will be automatically created and you won’t have these problems.
- Marked as answer by
  
  Friday, February 21, 2020 9:44 AM

Источник

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.

Источник

Remove From My Forums

Question
Below is the error message I am getting when I run the below script. Any help appreciated.

Msg 8114, Level 16, State 5, Line 57
Error converting data type nvarchar to bigint.
Warning: Null value is eliminated by an aggregate or other SET operation.

SELECT   L.ClientID,L.[GPRA Measures],L.AwardTitle,L.GrantNo,L.GranteeName,L.GranteeID,L.InterviewDateatIntake, L.InterviewDateatFollowup
               ,L.ProgramID,L.[Program Title], L.ProgramCode, L.Month,L.FFY,L.QTR,L.FYQTR,L.ThreeMonthFollowUpDue
               ,L.ThreeMonthFollowUpReceived,L.ValidCase,L.ValidResponseAtIntake,L.ValidResponseAtFollowUp

   FROM     RPT.Vw3MonthChangeLegacy AS L INNER JOIN
              rpt.vwCDPUserRole ur ON ur.CDPUserId = 25 INNER JOIN

              rpt.vwUserRoleEntity ure ON ur.Id = ure.UserRoleId and ure.EntityId = (CASE WHEN ure.EntityTypeCode = ‘AWARD’ THEN L.AwardTitle

              WHEN ure.EntityTypeCode =’SITEAWARD’ THEN L.SiteAwardId END)

Answers

Hi Naveen,

Pleace check the below condition
```
WHEN ure.EntityTypeCode ='SITEAWARD' THEN L.SiteAwardId END
```
You need to use either convert or cast the values in L.SiteAwardId column. I think its coming as bigint.

Hope this help you.

Thanks

Suhas

Mark as Answer if this resolves your problem or «Vote as Helpful» if you find it helpful.

My Blog

Follow @SuhasKudekar
- Marked as answer by
  
  Thursday, February 5, 2015 4:42 PM

Источник

Question

Answers

Question

Answers

Question

Answers

My Blog

Follow @SuhasKudekar