SQL Server conversion failed when converting the nvarchar value '-49.15' to data type int

Question

I received this error:

SQL Server Conversion failed when converting the nvarchar value '-49.15' to data type int

in my case statement.

[LINE1_OPEN_LOOP_VOLTAGE] and [LINE2_OPEN_LOOP_VOLTAGE] are both nvarchar datatypes.

I have negative, positive, and null values in the table. Can I set these fields to an integer before the SELECT statement?

CASE 
WHEN [LINE1_OPEN_LOOP_VOLTAGE]>= 0 OR [LINE2_OPEN_LOOP_VOLTAGE]>= 0 THEN 'YES'
WHEN [LINE1_OPEN_LOOP_VOLTAGE] IS NULL OR [LINE2_OPEN_LOOP_VOLTAGE] IS NULL THEN 'NO'
ELSE 'CHECK BUSINESS LOGIC'
END AS 'REVERSE POLARITY ISSUE',

Answer 1

I think your value is float type or numeric type (-49.15 from nvarchar value). Then, you should convert nvarchar type to float type or numeric type. Try something like this:

DECLARE @LINE1_OPEN_LOOP_VOLTAGE NVARCHAR(10) = '-49.15'
DECLARE @LINE2_OPEN_LOOP_VOLTAGE NVARCHAR(10) = '49.15'
SELECT CASE 
    WHEN CONVERT(FLOAT, @LINE1_OPEN_LOOP_VOLTAGE) >= 0 -- OR NUMERIC
      OR CONVERT(FLOAT, @LINE2_OPEN_LOOP_VOLTAGE) >= 0 THEN 'YES'
    WHEN @LINE1_OPEN_LOOP_VOLTAGE IS NULL
      OR @LINE2_OPEN_LOOP_VOLTAGE IS NULL THEN 'NO'
    ELSE 'CHECK BUSINESS LOGIC'
    END AS 'REVERSE POLARITY ISSUE'

Answer 2

Just change the comparisons from integers to floating point numbers:

(CASE WHEN LINE1_OPEN_LOOP_VOLTAGE >= 0.0 OR LINE2_OPEN_LOOP_VOLTAGE >= 0.0
      THEN 'YES'
      WHEN LINE1_OPEN_LOOP_VOLTAGE IS NULL OR LINE2_OPEN_LOOP_VOLTAGE IS NULL
      THEN 'NO'
      ELSE 'CHECK BUSINESS LOGIC'
 END) AS [REVERSE POLARITY ISSUE],

In SQL Server 2012+, you can use try_convert():

(CASE WHEN try_convert(float, LINE1_OPEN_LOOP_VOLTAGE) >= 0.0 OR 
           try_convert(float, LINE2_OPEN_LOOP_VOLTAGE) >= 0.0
      THEN 'YES'
      WHEN LINE1_OPEN_LOOP_VOLTAGE IS NULL OR LINE2_OPEN_LOOP_VOLTAGE IS NULL
      THEN 'NO'
      ELSE 'CHECK BUSINESS LOGIC'
 END) AS [REVERSE POLARITY ISSUE],

Answer 3

Your data is going to have some serious issues at some point - storing integers in text-based fields produces all sorts of problems with converting on the fly. If you can't clean that up, we need to get a little tricky and make a heap of assumptions e.g. that any NULL will defeat any numeric value.

I'm going to assume you have an ID of some sort to query on, and I'll build a little table for a demo:

CREATE TABLE mynvarcharstuff (ID INTEGER, LINE1_OPEN_LOOP_VOLTAGE nvarchar(50), LINE2_OPEN_LOOP_VOLTAGE nvarchar(50))

INSERT INTO mynvarcharstuff VALUES (1, '-49.15', '-8.68'),  (2, '-49.15', NULL),    (3, '-18', '7.65'), (4, '26.5', '4')
,   (5, '49.15', NULL), (6, '25.3', '-12.4'),   (7, NULL, NULL),    (8, 'bob', '6.5'),  (9, ' 33', '')

Solving the issue of converting on the fly is done using a CTE (or temp table if you like) to sort it out as you go, based on those IDs:

WITH numeric_ids AS (
    SELECT  ID
    ,       CASE WHEN (LINE1_OPEN_LOOP_VOLTAGE IS NULL OR LINE2_OPEN_LOOP_VOLTAGE IS NULL) THEN 1 ELSE 0 END AS CheckNull
    ,       CASE WHEN ISNUMERIC(LINE1_OPEN_LOOP_VOLTAGE) = 1 THEN 1 ELSE 0 END AS CheckLine1
    ,       CASE WHEN ISNUMERIC(LINE2_OPEN_LOOP_VOLTAGE) = 1 THEN 1 ELSE 0 END AS CheckLine2
    FROM    mynvarcharstuff
)
SELECT N.ID
, CASE
    -- If you move this line below either of the numeric checks, you potentially get errors - be careful!
    WHEN I.CheckNull = 1 THEN 'NO'  
    WHEN (I.CheckLine1 = 1 AND CONVERT(DECIMAL(5, 2), [LINE1_OPEN_LOOP_VOLTAGE]) >= 0.0) THEN 'YES'
    WHEN (I.CheckLine2 = 1 AND CONVERT(DECIMAL(5, 2), [LINE2_OPEN_LOOP_VOLTAGE]) >= 0.0) THEN 'YES'
    ELSE 'CHECK BUSINESS LOGIC'
  END AS 'REVERSE POLARITY ISSUE'
FROM mynvarcharstuff N
JOIN numeric_ids I ON I.ID = N.ID

As we can't convert the columns on the fly, we need to first check they're able to be converted, then see if they're positive or NULL.

Note this isn't a perfect solution. If you have rules around whether NULL must exist in Line1 if it appears in Line2 and vice-versa, it works.

But you're better off cleaning the data with something like this:

DELETE
FROM    mynvarcharstuff
WHERE   (ISNUMERIC(LINE1_OPEN_LOOP_VOLTAGE) = 0 AND LINE1_OPEN_LOOP_VOLTAGE IS NOT NULL)
OR      (ISNUMERIC(LINE2_OPEN_LOOP_VOLTAGE) = 0 AND LINE2_OPEN_LOOP_VOLTAGE IS NOT NULL)