question

Katie 1 avatar image
Katie 1 asked

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-2008sqlt-sqlcast-convert
10 |1200

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

1 Answer

·
Daniel Ross avatar image
Daniel Ross answered

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 comment
10 |1200

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

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+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 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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