CONVERT function doesn't work properly on database operators

Document ID : KB000100196
Last Modified Date : 05/06/2018
Show Technical Document Details
Question:
When using a CONVERT function on a database operator the result is different to what the DBMS, Why?
Answer:
Database operator can run different kind of SQL statements and queries, but some of them can work differently to what the designer actually expects.

CONVERT() is a function that can convert an expression from certain data type to another, but using this on a database operator may result in a non expected result, just as the following example:

1.- Write a standard CONVERT statement on a DBMS and tests the result:

User-added image
2.- Configure a database operator with this same script in it:

User-added image

3.- Running this process, the result is different to what the actual query returned the first time:

User-added image

NOTE: The result is 2 days less than the actual query output.

This happens because CONVERT() is a non ANSI-SQL compliant function. In order to return the actual result, the function CAST() can be used, which is SQL-92 standard compatible.

Using CAST() function:

1.- Write same query than before, but using CAST() function on the DBMS and then test the result:

User-added image
2.- Configure a new database operator using above script:

User-added image

3.- Running this new instance will show the expected result:

User-added image