Issue with the cast from varchar to numeric!

Question

question

Katie 1 asked Apr 11, '10

Issue with the cast from varchar to numeric!

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,

sql-server-2008 sql t-sql cast-convert

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-12T02:50:13Z

Daniel Ross answered Apr 12, '10

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.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ commented · Apr 12, 2010 at 04:11 AM

+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.

2 ·

question