If I run the SQL query below; I get the following error:
Error converting data type nvarchar to numeric.
COLUMNA contains only numbers (negative and positive) including fields with maximal up to two digits after the decimal and is stored as dot decimal.
IF OBJECT_ID('st00_TABLEA','U') IS NOT NULL DROP TABLE [st00_TABLEA] SELECT COLUMNA AS COLUMNA_s ,CASE WHEN [COLUMNA] = '' THEN 0 ELSE CONVERT(NUMERIC(18,2),REPLACE([COLUMNA],',','.')) END AS COLUMNA INTO st00_TABLEA FROM dbosu.TABLEA; I also tried the following, but still same problem:
IF OBJECT_ID('st00_TABLEA','U') IS NOT NULL DROP TABLE [st00_TABLEA] SELECT COLUMNA AS COLUMNA_s ,CONVERT(DECIMAL(18,2),COLUMNA) AS COLUMNA INTO st00_TABLEA FROM dbosu.TABLEA; 3 Answers
You might need to revise the data in the column, but anyway you can do one of the following:-
1- check if it is numeric then convert it else put another value like 0
Select COLUMNA AS COLUMNA_s, CASE WHEN Isnumeric(COLUMNA) = 1 THEN CONVERT(DECIMAL(18,2),COLUMNA) ELSE 0 END AS COLUMNA 2- select only numeric values from the column
SELECT COLUMNA AS COLUMNA_s ,CONVERT(DECIMAL(18,2),COLUMNA) AS COLUMNA where Isnumeric(COLUMNA) = 1 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.
And
SELECT try_convert(decimal(15,2), '2.81104e+006') returns NULL.
SELECT convert(float, '2.81104e+006') returns the correct value 2811040.
I was running into this error while converting from nvarchar to float.
What I had to do was to use the LEFT function on the nvarchar field.
Example: Left(Field,4)
Basically, the query will look like:
Select convert(float,left(Field,4)) from TABLE Just ridiculous that SQL would complicate it to this extent, while with C# it's a breeze!
Hope it helps someone out there.
