SYMPTOM:
After upgrading from Strategy 10.4 to 10.11, some reports can not be executed due the following error: 'Explicit conversion from data type int to date is not allowed'.
This occurs against SQL Server databases and the differences can be seen in the SQL. Strategy uses Convert functions in 10.4 and Cast functions in 10.11.
There are no changes in the VLDB properties or objects on the report. This occurs with attributes/metrics that make use of the date and trunc functions but then have formulas which do further calculation not supported by the output of those functions.
The working SQL from 10.4
select (CONVERT(DATETIME, CONVERT(VARCHAR(10), a11.ID_DEPARTURE_DATE, 101)) - CONVERT(DATETIME, CONVERT(VARCHAR(10), a11.ID_SNAP_SHOT_DATE, 101))) CustCol_5,
a11.ID_DEPARTURE_DATE ID_DATE,
max(a13.date) date,
a13.AjusteDiaSemana AjusteDiaSemana,
max(a13.DS_DAY_WEEK) DS_DAY_WEEK,
a12.DS_VALID_ROUTE DS_VALID_ROUTE,
a12.DS_VALID_ROUTE_RT DS_VALID_ROUTE_RT,
a11.ID_FLIGHT_NUMB ID_FLIGHT_NUMB,
a11.ID_SNAP_SHOT_DATE ID_DATE0,
The broken SQL from 10.11
select (CAST(a11.ID_DEPARTURE_DATE AS DATE) - CAST(a11.ID_SNAP_SHOT_DATE AS DATE)) CustCol_5,
a11.ID_DEPARTURE_DATE ID_DATE,
max(a13.date) date,
a13.AjusteDiaSemana AjusteDiaSemana,
max(a13.DS_DAY_WEEK) DS_DAY_WEEK,
a12.DS_VALID_ROUTE DS_VALID_ROUTE,
a12.DS_VALID_ROUTE_RT DS_VALID_ROUTE_RT,
a11.ID_FLIGHT_NUMB ID_FLIGHT_NUMB,
a11.ID_SNAP_SHOT_DATE ID_DATE0,
CAUSE:
This is caused by changes in the default function syntax for the functions 'Date' and 'Trunc' between Strategy 10.4 and 10.11 for SQL Server. These functions however must be used in a manner that is not consistent with their input or output datatypes. The date function is designed to return the date component of a datetime datatype value. The trunc function returns the integer part of a decimal. Considering these outputs it is not necessarily valid to perform formulas such as date(column1) - date(column2) unless the database naturally supports subtraction on a date datatype column. The old function syntax handles this while the new simpler syntax which performs better does not handle those sorts of odd formula.
ACTION:
The correct solution is to change the formula to be more explicitly valid for the calculations in question. When subtracting one date from another for example the datediff function should be used. This means instead of a formula of "date(column1) - date(column2)" the formula should be "datediff(date(column1),date(column2))". The datediff function is designed explicitly for getting the difference between two date datatype values unlike the subtraction function.
WORKAROUND:
To go back to the old SQL, please follow below steps:
1. Backup the DATABASE.PDS file on a client (Developer) machine.
2. Change the existing DATABASE.PDS file entry for <FUNCTION_REF ID="C50F2A4F75AE49d6B20E00224967B500" and "F262C82FDDA611D3B98100C04F2233EA" under the SQL server 2012 dbms (or other appropriate SQL Server dbms that is in use), to use "CONVERT(DATETIME, CONVERT(VARCHAR(10), #0, 101))" />
3. Update the dbms in Strategy by editing the database instance and upgrading the database connection type object (use the steps contained in the article KB43537).
4. Restart the I-Server.
5. Purge the report cache, then the report SQL will change back to the original one.
KB441127
