Error converting data type nvarchar to numeric. in Sql Serve

Question

Error show Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to numeric. query -------- Select ROW_NUMBER() over(ORDER BY DATENAME(MONTH, convert(datetime,Date,105))+'-'+DATENAME(year, convert(datetime,Date,105)) ASC) as ID, DATENAME(MONTH, convert(datetime,Date,105))+'-'+DATENAME(year, convert(datetime,Date,105)) as Month, sum(cast(Amount as numeric(18,2))) as Amount, sum(cast(Addcost as numeric(18,2))) as Addcost , sum(cast(Discount as numeric(18,2))) as Discount , sum(cast(Tax_Amount as numeric(18,2))) as Tax_Amount, sum(cast(Total_Amount as numeric(18,2))) as Total_Amount from MainBillForm where Sno=0 group by DATENAME(MONTH, convert(datetime,Date,105))+'-'+DATENAME(year, convert(datetime,Date,105)) ORDER BY DATENAME(MONTH, convert(datetime,Date,105))+'-'+DATENAME(year, convert(datetime,Date,105))

Rafnas T P · Answer

I think in your table columns Amount,Addcost,Discount,Tax_Amount,Total_Amount are nvarchar please check these columns are having null or empty

Nigel Fernandes · Answer

Why are you casting those columns to numeric(18,2) ? I'm assuming those columns are actually in varchar. If that is the case add a where clause of each of those columns such as: where ISNUMERIC( Amount) =1 and isnumeric(...)

Sudhir Mittal · Answer

your nvarchar columns have empty values. If you have SQL Server 12 then you can use iif(Amount='',0,Amount) before casting to numeric, otherwise use case when Amount='' then 0 else Amount end before casting.

Rafnas T P · Answer

then please update those field to zero or in select statement before converting check if it is numeric

Jyoti Jodha · Answer

yes, Rafnas Amount,Addcost,Discount,Tax_Amount,Total_Amount are nvarchar And columns are having null or empty values

Vijay Kumar · Answer

you can use sum(cast(Amount as money)) as Amount instead of sum(cast(Amount as numeric(18,2))) as Amount,