CODEWITHSUNDEEP
sqlsql-servert-sql
Pete Whelan
Pete Whelan

Reputation: 95

Out of nowhere, SQL Conversion error on a simple query

On a system which has worked fine for years - just today I'm noticing an error.

The mind-blowingly simple

SELECT InvoiceID
FROM t_Invoices
WHERE DocumentNumber = 3841734

is now giving an error:

Conversion failed when converting the varchar value '3841730.' to data type int.

I have no idea where the 3841730 value is coming from (although its suspiciously close to the DocumentNumber value - 3841734) I am only selecting InvoiceID so why is it giving this error.

All the data for this row is as follows:

InvoiceID   DocumentNumber  Date_Created    IsActive    Date_LastOperated   Date_Closed WF_CurrentState WF_PreviousState    WF_CurrentUser  WF_CurrentCredDeptUser  WF_InitialCredDeptUser  WF_PreviousUser WF_WithCreditorsDept    ApprovedAboveLimit  Approved    DocumentSpec    CostCentreID    Date_Due    PaymentInterestComment  PaymentInterestFlag PaymentInterestCommentFreeText  DocumentSpecOriginal
832128  3841734 2021-04-23 08:57:05.000 1   2021-04-23 08:57:05.000 NULL    1   1   15  15  15  15  1   0   0   \\invoicerouting\DocumentStore\2021\04-Apr\UP-20210423-3841734.pdf  1   NULL    NULL    NULL    NULL    NULL

Thanks

Upvotes: 1

Views: 1108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

This would appear to be causing the problem:

WHERE DocumentNumber = 3841734

The issue is that you are mixing types. In this case, DocumentNumber would appear to be a string not a number. And, SQL (all dialects) prefer numbers to strings, so it attempts to convert the column DocumentNumber to a string.

The value:

3841730.

is a value in the column. You may or may not notice, but it appears to have a decimal point. Oh, how sad.

You should fix the data. But in the meantime, fix the query! Don't mix data types:

WHERE DocumentNumber = '3841734'

What can you do to fix the data? First, find offending values:

select DocumentNumber
from t_Invoices
where try_convert(int, DocumentNumber) is null and DocumentNumber is not null;

After you have fixed the data, you can then do one of two things:

  1. If you decide that you really want a string, then add a check constraint to only allow digits (or whatever is allowed).
  2. If you decide that the value is really a number, then change the type to int -- or if you need larger values, to a numeric.

I would go for (2). A distinguishing feature for (1) would be if leading zeros are important.

Upvotes: 8

Related Questions