Issue with the cast from varchar to numeric!

Question

Hi all,

I am trying to implement this in a view, and i recieve this error, any idea how to fix it?

   SELECT Report_ID, Report_Name, Customer_ID, Username, 
   CAST(C1 AS NUMERIC(13, 2)) AS DA_ENERGY, 
   CAST(C2 AS NUMERIC(13, 2)) AS RT_ENERGY, 
   CAST(C3 AS NUMERIC(13, 2)) AS REGULATION, 
   CAST(C4 AS DATETIME) AS DA_VERSION, 
   CAST(C5 AS DATETIME) AS RT_VERSION, 
   CAST(C6 AS NUMERIC(13, 2)) AS RESERVE,
   CAST(C7 AS DATETIME) AS REGULATION_VERSION, 
   CAST(C8 AS DATETIME) AS RSV_VERSION
   FROM         ISO.ISO_HR_DATA
   WHERE     (Report_Name = 'BL_DETAILS')

Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to numeric.

all the values are stored as varchar in the database.

thanks,

Answer 1

0

I would take out all of the cast functions, and see which column has non-numeric value using the 'where isnumeric(C2)=0' function this returns all rows where C2 has a value that is not numeric and you can fix the errors or delete those rows if they definitely must be numeric.

Then in your main query add 'where isnumeric(C2)=1' and this will ensure that only numeric values are returned.

answered Apr 12 '10 at 02:50 AM

Daniel Ross
2.9k ● 6 ● 11 ● 12

+1 - but one thing to note is that ISNUMERIC will return 1 for a varchar that can convert to any type of number. Therefore it will include strings with 'E' and 'D' in them (for floating point number conversion). Seeing as numeric won't handle scientific notation types, it is worth coding for this case too.

Apr 12 '10 at 04:11 AM Matt Whitfield ♦♦

+1 on your comment, Matt.

May 14 '10 at 09:45 PM Jeff Moden

(comments are locked)

Issue with the cast from varchar to numeric!

Follow this question

Related Questions