IBM Support What's New?

Error formatting dates on SQL Server

Technote (troubleshooting)


Problem(Abstract)

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.

Error -239:
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"

Cross reference information
Segment Product Component Platform Version Edition
Business Analytics Cognos 8 Business Intelligence Install and Config
Business Analytics Cognos Series 7 Impromptu

Historical Number

111111

Document information

More support for: Cognos Series 7
Impromptu

Software version: Cognos 8 BI Install and Config 8.1, Impromptu 6.0, Impromptu 7.3

Operating system(s): Linux, Platform Independent, Windows

Reference #: 1337136

Modified date: 2014-09-12