One of the forum member had posted a question yesterday saying she was trying to use a CASE statement and it was throwing an error as "Error converting data type varchar to float".
Let us reproduce the scenario and see the way to fix it.
--Sample table
Create table dbo.ts_vouchers
(
Lodging_Amount float
)
--Test records
Insert into dbo.ts_vouchers values (0)
Insert into dbo.ts_vouchers values (10)
Insert into dbo.ts_vouchers values (1.1)
Insert into dbo.ts_vouchers values (0)
/*
We need to display hyphen (-) if Lodging_Amount is 0.
The below query would throw the error
*/
SELECT
CASE Lodging_Amount
WHEN 0 THEN '-'
ELSE Lodging_Amount
END AS "Lodging Amount"
From dbo.ts_vouchers
Solution:
/*
We need to convert the Float value Lodging_Amount as Varchar
before displaying it.
*/
SELECT
CASE Lodging_Amount
WHEN 0 THEN '-'
ELSE Cast(Lodging_Amount as Varchar)
END AS "Lodging Amount"
From dbo.ts_vouchers
Comments