I think I have discovered something that is possibly significant!

As stated previously, the data is a set of key/value pairs, with a datatype (D = Date, N= Number, S = String, B = Bit)

If the table contains only data of type D, my query works, but if there is data of any other type in the table, I get the conversion error message, even though my WHERE clause explicitly excludes everything except those where the datatype = 'D'. This suggests that it is evaluating the date comparison criteria before the datatype = 'D' clause, and the conversion is failing on non-date datatypes.

I therefore put the WHERE datatype = 'D' selection into a derived view of the table, assuming this would be evaluated before the date comparisons later in the query, but this is appears not to be the case unless I put in a kludge to force the matter

This fails:

SELECT DERIVED.*

FROM (select parameter_value PV , ,convert(datetime2,PV.parameter_value) as key_date WHERE PV.datatype = 'D') DERIVED

WHERE DERIVED.key_date BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())

But this works!

SELECT DERIVED.*

FROM (select TOP 9999999999 parameter_value PV , ,convert(datetime2,PV.parameter_value) as key_date WHERE PV.datatype = 'D') DERIVED

WHERE DERIVED.key_date BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())