Error converting data type nvarchar to numeric.
July 27, 2010
I’ve come to the conclusion that MSSQL is a whiney little bitch. However I’ve found out a way to deal with MSSQL in this case. The solution is pretty simple however not the easiest to come bye. Read on for the solution.
Recently I was trying to do a SUM of a column. However I kept on getting Error converting data type nvarchar to numeric. I was somewhat perturbed and then started scouring the net for an answer. Luckily enough I stumbled onto a post here. Apparently you get “Error converting data type nvarchar to numeric” if there is a NULL in the column. Dumb huh?
So after knowing this the solution was pretty easy.
select a.id, b.mynumber, sum(cast(a.size AS numeric(18,0))) as size
from document a, property b
where a.id=b.id
group by a.id,b.mynumber
order by b.mynumber
This here threw the error. But a simple way to get over this is to do this.
select a.id, b.mynumber, sum(cast(a.size AS numeric(18,0))) as size
from document a, property b
where a.id=b.id
– we must check for size or we get an error
and a.size > ’0′
group by a.id,b.mynumber
order by b.mynumber
Simply checking for a size > 0 will side step this error. Since we are trying to SUM sizes in a column this helps alot.
hope you guys find this useful.
Got something to say?
You must be logged in to post a comment.
