x

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,

more ▼

asked Apr 11 '10 at 11:32 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 129 163 202

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Apr 12 '10 at 02:50 AM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 13

+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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x977
x672
x29

asked: Apr 11 '10 at 11:32 PM

Seen: 2726 times

Last Updated: Apr 12 '10 at 03:44 AM