convert returns conversion error but try_convert does return the correct value

Question

Hi All,

I have the following case statement in a TSQL script, which returns the correct negative decimal value, in the rows that acually have a value

    try_convert (decimal (18,6), max (case
        when [databody.daysQuality.testResults.testType] = 'Freezing Point' and [databody.daysQuality.testResults.testType] is not null
        then [databody.daysQuality.testResults.value]
        else ''
    end)) as [freezing_point]

However, when I change try_convert to convert it will fail with : "Error converting data type varchar to decimal"

Am I missing something here?

Cheers

Hans

Answer 1

Well, this fails:

SELECT convert(decimal(18, 6), '')

Not sure why you have that ELSE there. I would think that returning NULL would be better. But I don't know your business rules. Anyway, the empty string seems out of place.

Answer 2

We only see a snippet of your code, so we are left to guesses.

However, a common situation is like in this example:

SELECT a, convert(int, strcol)
FROM   tbl 
WHERE  rowtype = 6

And for rowtype = 6, all values in strcol are integer values. Whereas for rowtype = 4 there are strings, rowtype = 5 there are dates etc.

So logically, this query should not fail. However, the optimizer may get the idea to perform the conversion before filtering for the WHERE clause and the query dies with a conversion error. Changing to try_convert avoids this problem.

Could this apply to your case?