3

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 
9

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.