SQL Server 2008: Error converting data type nvarchar to float

Presently troubleshooting a problem where running this SQL query:

UPDATE tblBenchmarkData 
SET OriginalValue = DataValue, OriginalUnitID = DataUnitID, 
    DataValue = CAST(DataValue AS float) * 1.335 
WHERE 
    FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' 
    AND ZEGCodeID IN 
             (SELECT ZEGCodeID FROM tblZEGCode 
              WHERE(ZEGCode = 'C004') OR 
                   (LEFT(ZEGParentCode, 4) = 'C004'))

Results in the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

The really odd thing is, if I change the UPDATE to SELECT to inspect the values that are retrieved are numerical values:

SELECT DataValue 
FROM tblBenchmarkData 
WHERE FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' 
AND ZEGCodeID IN 
         (SELECT ZEGCodeID 
          FROM tblZEGCode WHERE(ZEGCode = 'C004') OR 
                               (LEFT(ZEGParentCode, 4) = 'C004'))

Here are the results:

DataValue
2285260
1205310

Would like to use TRY_PARSE or something like that; however, we are running on SQL Server 2008 rather than SQL Server 2012. Does anyone have any suggestions? TIA.

It would be helpful to see the schema definition of tblBenchmarkData, but you could try using ISNUMERIC in your query. Something like:

SET DataValue = CASE WHEN ISNUMERIC(DataValue)=1 THEN CAST(DataValue AS float) * 1.335 
                     ELSE 0 END

Order of execution not always matches one's expectations.

If you set a where clause, it generally does not mean the calculations in the select list will only be applied to the rows that match that where. SQL Server may easily decide to do a bulk calculation and then filter out unwanted rows.

That said, you can easily write try_parse yourself:

create function dbo.try_parse(@v nvarchar(30))
returns float
with schemabinding, returns null on null input
as
begin
  if isnumeric(@v) = 1
    return cast(@v as float);

  return null;
end;