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.
Answer by Daniel Ross ·
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.