The following error occurs in Impromptu 6.0 when using SQL Server 6.5 via an ODBC connection and performing a filter on dates (e.g. between startdate and enddate) using Prompts. The DB column is of type datetime.
DMS-E-GENERAL, Error during operation ¿prepare request' DMS-E-MATHEXCEPTION, arithmetic error
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of CHAR to DATETIME resulted in a DATETIME value out of range.
Resolving the problem
The error results from non-SQL 92 compliant formatting of dates on the SQL Server (in this case a manual SQL in iSQL requires the user to type in a date in format yyyy-dd-mm).
The report works when using an Impromptu function to convert datetime to date. However, with a high volume of data this would lead to a slowdown in performance due to local processing of the Impromptu functions.
To resolve the issue, make a backup copy of your cogdmod.ini file. Now modify cogdmod.ini by adding the following entry in the MICROSOFT SQL SERVER section:
[Exceptions Literals DATABASE:MICROSOFT SQL SERVER]
Timestamp_Format_Str="YYYY DD MM HH MM SS"
|Business Analytics||Cognos 8 Business Intelligence||Install and Config|
|Business Analytics||Cognos Series 7||Impromptu|