Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

MSSQL 2012 - Why is AVG & TRY_CONVERT not returning the correct value?

on Wed Dec 13, 2017 11:29 am
Im using MSSQL 2012 and iam trying to use AVG together with TRY_CONVERT on a table column with the following datatype:

nvarchar(255), NOT NULL
First before i try to query using AVG & TRY_CONVERT, this is the data that i want to get the AVG value out of using this query:

And this is the results after using AVG and TRY_CONVERT, 0 rows returned.

UPDATE!: When i execute the query below which target data that has a "." separator (qtv2.qtv_qteid = 58 (instead of 63)) , it works! So the issue is the "," separator. Anyone know solution to this??
Code:
declare @ProjectSelection nvarchar(10)
set @ProjectSelection = 'C82007588'
SELECT AVG(TRY_CONVERT(numeric(10,5), avgcap)) FROM ( select qtv2.qtv_result as avgcap from ProductionOrder PO left join CustomerOrder co on co.CustomerOrderId=po.customerorderid left join ProductionOrderProperty pop on pop.ProductionOrderId=po.productionorderid left join product p on p.ProductionOrderId=po.productionorderid left join QualityTestValues qtv on qtv.qtv_productid=p.ProductId left join QualityTestValues qtv2 on qtv2.qtv_productId=p.ProductId
Code:
where pop.Value=@ProjectSelection and pop.name = 'project' and po.ProductTypeId = 1
and qtv2.qtv_qteid = 58 and qtv2.qtv_valid = 1 and qtv.qtv_ProductSegmentId = 144 and qtv.qtv_valid = 1
and qtv.qtv_qteid = 51 and qtv.qtv_result = 'J'

group by co.CustomerName, pop.Value, qtv2.qtv_result, p.SerialNumber

Answers 1

down vote
How about your compatibility level? A similar question can be found here:

TRY_CONVERT fails on SQL Server 2012

Although your server version is 2012, a lower compatibility level can cause the try_convert to be unavailable for use in your database. You can check this by running the following code in your specific database and afterwards on for instance the master database.
Code:
DECLARE @b VARCHAR(10) = '12312'
SELECT TRY_CONVERT(INT,@b)

ou can take your first query and do something like this:
Code:
SELECT AVG(TRY_CONVERT(numeric(10,5), avgcap))
FROM
(
    -- Insert your first query here.
) A
This should give you the average of the numbers.

Back to top
Permissions in this forum:
You can reply to topics in this forum