Saturday, September 03, 2011

Error converting data type varchar to float

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

No comments: