Convert Varchar To Money

Question

I am trying to convert a varchar column to money but I keep getting this error

Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.

Am I not using the convert function like the error suggests?

Select
    tellerID,
    SUM(CASE 
           WHEN CONVERT(money, value1) IS NULL THEN '0' 
           ELSE CONVERT(money, value1) 
        END) AS [Total Value 1]
From 
    intakedrawer
Group By 
    tellerID
Order By 
    tellerID

Solution 1

Try like this:

Select
tellerID,
SUM(CASE WHEN IsNumeric(value1) = 0 THEN 0 
       ELSE CAST(value1 AS decimal(18, 2)) 
END) as [Total Value 1]
From intakedrawer
Group By tellerID
Order By tellerID

Solution 2

If you're on SQL Server 2012 or higher you can also use TRY_CONVERT. If the conversion doesn't work it returns NULL.

SELECT
 tellerid,
 SUM(TRY_CONVERT(MONEY, value1)) AS [Total Value 1]
FROM intakedrawer
GROUP BY tellerid
ORDER BY tellerid;

There's a SQL Fiddle here.

Comments

[Rashid Abib]

Sorry, I should have indicated that in my initial post, this is SQL 2008

[Ed Gibbs]

No worries. In that case Rahul's answer is the one you want. Please be sure to mark it as accepted if it works for you :)