CODEWITHSUNDEEP
sqlcastingtype-conversionssms-2012
LempiM
LempiM

Reputation: 11

Error converting data type varchar to decimal, --data type errors

I am altering a view to cast geographic coordinates (numbers with varying decimal precision) into a decimal field.

I have confirmed that the only items in the source tables are numbers and decimals -- but I am getting the error

"Error converting data type varchar to numeric."

Is it possible the decimal in the source table is being read as a character, and if so, what could I do to successfully execute this conversion?

ALTER VIEW [SCHEMA].[VIEW_V]
AS SELECT   
        cast(field 1 AS decimal (26,19)) as x_coord         
   FROM   [linkedServer].[Sourcedatabase].[schema].[dt_table]

Upvotes: 1

Views: 7148

Answers (2)

Chandrika
Chandrika

Reputation: 207

In case of float values with characters 'e' '+' it errors out if we try to convert in decimal. ('2.81104e+006'). It still pass ISNUMERIC test.

SELECT ISNUMERIC('2.81104e+006') returns 1.

SELECT convert(decimal(15,2), '2.81104e+006') returns error: Error converting data type varchar to numeric.

SELECT try_convert(decimal(15,2), '2.81104e+006') returns NULL.

SELECT convert(float, '2.81104e+006') it returns correct value 2811040.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Trying using isnumeric() or try_convert():

ALTER VIEW [SCHEMA].[VIEW_V] AS
    SELECT (CASE WHEN isnumeric(field1) = 1 
                 THEN cast(field1 AS decimal(26,19)) 
            END)as x_coord
    FROM   [linkedServer].[Sourcedatabase].[schema].[dt_table];

or

ALTER VIEW [SCHEMA].[VIEW_V] AS
    SELECT try_convert(decimal(26, 19), field1) 
    FROM   [linkedServer].[Sourcedatabase].[schema].[dt_table];

Where the resulting value is NULL, you will know which rows are causing the problems.

Upvotes: 4

Related Questions