CODEWITHSUNDEEP
sql-server
smpa01
smpa01

Reputation: 4346

SQL Server - Error converting data type varchar to bigint

I am not sure what I am doing wrong but I am getting the following error in a subquery

Select*
from
(select 

CONVERT (BIGINT,AX$AC1) as [Account]
from 
[x].[y]
where [AXOBJ] >399999 AND [AX$AC1] NOT like '%NON BENCHMARK%')a
where a.[Account]>=510980

It returns Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint.

I ran following to capture the data type of this table

select * from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='y'

| COLUMN_NAME | DATA_TYPE |
|-------------|-----------|
| AXOBJ       | char      |
| AX$AC1      | char      |

The following code return 39 integer values

select 

CONVERT (BIGINT,AX$AC1) as [Account]
from 
[x].[y]
where [AXOBJ] >399999 AND [AX$AC1] NOT like '%NON BENCHMARK%' GROUP BY AX$AC1

I am not sure where it going wrong.

Upvotes: 1

Views: 2898

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use try_convert() :

select [AX$AC1], [Account]
from [x].[y] cross apply
     ( values (try_convert(BIGINT, AX$AC1), try_convert(BIGINT, [AXOBJ])
              ) 
     ) a([Account])
where a.[AXOBJ] > 399999 AND 
      [y].[AX$AC1] NOT like '%NON BENCHMARK%' and
      a.[Account] > 510980;

You will find null values where conversation will fail by removing [Account] > 510980.

Upvotes: 1

J M
J M

Reputation: 1

I think the error is in your where condition where [AXOBJ] >399999, also the last where condition where a.[Account]>=510980.

Try the below code

Select*
from
(select 

CONVERT (BIGINT,AX$AC1) as [Account]
from 
[x].[y]
where CONVERT(bigint,[AXOBJ]) >399999 AND [AX$AC1] NOT like '%NON BENCHMARK%')a
where CONVERT(bigint,a.[Account]) >= 510980

Upvotes: 0

Related Questions