Msg 8114, Level 16, State 5, Line 33 Error converting data type varchar to numeric

Question

declare @rent decimal
set @rent = 1000
--select format(@rent,'C0')
select case when ISNULL(CAST(@rent AS varchar(50)),'') = '' then @rent else cast(format(@rent,'C') as varchar(50)) end AS Rent

I am getting this error while using the format function.

Does this answer your question? [Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric](https://stackoverflow.com/questions/19018377/msg-8114-level-16-state-5-line-1-error-converting-data-type-varchar-to-numeri) — Digvijay S, Apr 16 '20 at 05:48
You cannot return decimal in when clause and varchar in else clause. It must be a single data type. — shahkalpesh, Apr 16 '20 at 05:49
What is your database? And what is your requirement? What you wants to do with your input 1000? — mkRabbani, Apr 16 '20 at 05:50
What exactly is your question? The error itself is self-explanatory? — Dale K, Apr 16 '20 at 05:56
Getting the error in else block while using Format function, the casting i couldn't figure out — user10181000, Apr 16 '20 at 06:01

score 0 · Accepted Answer · edited Apr 16 '20 at 06:20

You can try this following:

declare @rent decimal
set @rent = 1000
--select format(@rent,'C0')
select 
case 
    when ISNULL(CAST(@rent AS varchar(50)),'') = '' then NULL
    -- I have used NULL as if the value = '' then it should be 
    -- presented by Null which is allowed for all data type
    else cast(format(@rent,'C') as varchar(50)) 
end AS Rent

In fact you can directly use the formatting as below as if the input is NULL, it will automatically return NULL. No need to go through a CASE expression.

declare @rent decimal
--set @rent = 1000
--select format(@rent,'C0')
select cast(format(@rent,'C') as varchar(50)) AS Rent

I want to suppress the null output that's why used case statement — user10181000, Apr 16 '20 at 06:16

Msg 8114, Level 16, State 5, Line 33 Error converting data type varchar to numeric

1 Answers1