When using a CONVERT function on a database operator the result is different to what the DBMS, Why?
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:
2.- Configure a database operator with this same script in it:
3.- Running this process, the result is different to what the actual query returned the first time:
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:
2.- Configure a new database operator using above script:
3.- Running this new instance will show the expected result: