IBM Support What's New?

Error formatting dates on SQL Server

Technote (troubleshooting)


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:

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


Document information

More support for: Cognos Series 7

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