Query won’t convert a nvarchar(30) to a decimal

I have the following query, that when it is run gives the following error:

Conversion failed when converting the nvarchar value '247.300' to data type int.

The query is as follows:

SELECT UD.SerialNumber,
       UD.ReadingDate,
       UD.ChannelName,
       CONVERT(decimal(18,2), ChannelValue) - LAG(CONVERT(decimal(18,2), ChannelValue),1,
       CONVERT(decimal(18,2), ChannelValue)) OVER (PARTITION BY ChannelName, 
       CASE ChannelValue WHEN 0 THEN 0 ELSE 1 END ORDER BY ReadingDate ASC)
FROM [Staging].[UriData] UD
WHERE UD.ChannelName IN (SELECT ChannelName FROM [Staging].[ActiveChannels])
ORDER BY ReadingDate;

Here is a fiddle: DB Fiddle

The only difference between the fiddle (which works fine) and the query here is that I have to have the ChannelValue column NVARCHAR as some of the data will be in text. I have filtered out all of the text data in the query.

I have also tested it with the following query and the data doesn’t show up. If it wasn’t a number I would expect it to appear here.

select ChannelValue
from [Staging].[UriData]
where try_convert(numeric(18, 2), ChannelValue) is null and ChannelValue is not null;

The expected outcome is that the number ‘247.300’ is converted into a number and processed. Why won’t it convert?

Answer

If you read the error message it complains about int.

this is from the implicit cast here

CASE ChannelValue WHEN 0 THEN 0 ELSE 1 END

Use an explicit cast

 CASE CONVERT(decimal(19,3), ChannelValue) WHEN 0 THEN 0 ELSE 1 END

then values such as 247.300 will convert fine and be comparable to the integer 0.

Though you also say “I have filtered out all of the text data in the query.”. In that case you should always use TRY_CONVERT anyway in case operations get ordered such that the conversion happens before the filter.