How To Sum A Varchar Data Type

In SQL Server 2008 I have a table with similar structure. Unfortunately changing the data structure is not an option, so I am stuck with attempting to come up with an alternative. I have tried to run this query, but I get an error of:


(5 row(s) affected)

Msg 8114, Level 16, State 5, Line 9

Error converting data type varchar to numeric.

Here is DDL - what syntax should be changed in the query in order for me to receive valid output and not an error message?

Declare @GreenHouse Table
(nomen varchar(100), vtc varchar(100), d1 date)

Insert Into @GreenHouse (nomen, vtc, d1) VALUES
('Green', '507.02', '2016-01-14'), ('Green', '4.44089e-015', '2016-01-03')
,('Green', '200.57', '2016-01-18'), ('Green', '649.01', '2016-01-19'),
('Green', '1849.85', '2016-04-30')

Select nomen, Round(Sum(Cast(vtc As Decimal(10,2))),0.00) As FormatedInfo, d1
FROM @GreenHouse Group By nomen, d1 Order By d1 DESC

The 4.44089e-015 is most likely giving you the error by trying to convert it to a decimal. Try using float instead:

SELECT  nomen, 
        ROUND(SUM(CAST(vtc AS float)),0.00) FormatedInfo, 
        d1
FROM @GreenHouse 
GROUP BY nomen, d1 
ORDER BY d1 DESC;

The results with your sample data are:

╔═══════╦══════════════╦════════════╗
║ nomen ║ FormatedInfo ║     d1     ║
╠═══════╬══════════════╬════════════╣
║ Green ║         1850 ║ 2016-04-30 ║
║ Green ║          649 ║ 2016-01-19 ║
║ Green ║          201 ║ 2016-01-18 ║
║ Green ║          507 ║ 2016-01-14 ║
║ Green ║            0 ║ 2016-01-03 ║
╚═══════╩══════════════╩════════════╝

(yeah, I know that it converts the 4.44089e-015 value to 0, but it is expected for a float)

StackExchange Database Administrators Q#176686, answer score: 10