CONVERT function limitations

The CONVERT function is not supported on the vValue column in an OPENQUERY statement. If you are using OPENQUERY on the History table, you must filter on the vValue column outside of the query.

In the following example, the value of the vValue column is converted to a float. Note that no string tags are included in the query.

SELECT * FROM OpenQuery(INSQL, 'SELECT DateTime, Quality, OPCQuality, QualityDetail, Value, vValue, TagName

FROM History

WHERE TagName IN ("SysTimeMin", "SysPulse")

AND DateTime >= "2001-12-30 04:00:00.000"

AND DateTime <= "2001-12-30 09:00:00.000"

AND wwRetrievalMode = "Delta"

')

WHERE convert(float, vValue) = 20.0

You can also use the following formats:

WHERE convert(float, vValue) = 0

WHERE convert(float, vValue) = 0.0

WHERE convert(float, vValue) = 1.0

WHERE convert(float, vValue) = 1

WHERE convert(float, vValue) = 20

WHERE convert(float, vValue) = 2.0000e01

The following example includes a string tag and converts the vValue value to a char or varchar datatype. All values returned can be converted to a string.

SELECT * FROM OpenQuery(INSQL, 'SELECT DateTime, Quality, OPCQuality, QualityDetail, Value, vValue, TagName

FROM History

WHERE TagName IN ("SysString", "SysTimeMin", "SysPulse")

AND DateTime >= "2001-12-30 04:00:00.000"

AND DateTime <= "2001-12-30 09:00:00.000"

AND wwRetrievalMode = "Cyclic"

AND wwCycleCount = 300

')

WHERE convert(varchar(30), vValue) = '2001-12-30 14:00:00'

You can also use the following formats:

WHERE convert(varchar(30), vValue) = '20'

WHERE convert(varchar(30), vValue) = '1'

WHERE convert(varchar(30), vValue) = '0'