Error converting data type nvarchar to numeric. in Sql Serve

Jyoti Jodha

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))

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P9y ago
Accepted 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
Vijay Kumar8y ago
you can use

sum(cast(Amount as money)) as Amount

instead of sum(cast(Amount as numeric(18,2))) as Amount,
Sudhir Mittal9y ago
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 P9y ago
then please update those field to zero or in select statement before converting check if it is numeric
Jyoti Jodha9y ago
yes, Rafnas

Amount,Addcost,Discount,Tax_Amount,Total_Amount
are nvarchar
And columns are having null or empty values
Nigel Fernandes9y ago
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(...)