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 Intelligence & Financial Performance Management Cognos 8 Business Intelligence Install and Config
Business Intelligence & Financial Performance Management Cognos Series 7 Impromptu
 
Historical Number
111111
 
 

Rate this page:

(0 users)Average rating

Add comments

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):

Legacy Cognos, Win2000, linuxPPC

Reference #:

1337136

Modified date:

2009-10-28

Translate my page

Machine Translation

Content navigation