Out-Of-Range Date Time Value:WIS 10901

building reports webintelligence
1

Hi,
While refreshing WEBI report,I am getting "“The Conversion of Char Data type to a date time data type resulted in an out-of-range date time Value :WIS 10901”.I m using SQL server.Any Solution for this?.Error got while giving Date Range Prompt value.

antojay (BOB member since 2007-04-26)

2

You need to change the setting in the parameter file sqlsrv.prm

For deski/full client, it’s at C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc\sqlsrv.prm

For webi, it’ll be on one of the servers somewhere (one of our techies just did it this morning for me, so I know it works, but don’t know exactly where the file is)

Change line
‘mm/dd/yyyy HH:m:s’
to
‘dd/mm/yyyy HH:m:s’

debbie

Debbie :uk: (BOB member since 2005-03-01)

3

Thanks,Let me know where the WEBI server file located and whats the name of the WEBI server file?..

antojay (BOB member since 2007-04-26)

4

Hello,

the solution for this problem was already familiar to me. However it seems to me this happens now in BOXI R3 another way. I changed the sqlsrv.prm in to the format “dd.mm.yyyy hh:mm:ss” restarted the entire server anew but it still creates some querries like “07/09/2008 00:0:0” which results in a error message (which is in german language however).

I try to translate:

Converting a char datatype to a datetime datatype, the datetime value is not in the valid range.

If I chane the sql manually to “09.07.2008 12:00:00” it works however.

Any ideas?

Jpod :de: (BOB member since 2008-02-25)

5

Try the locale settings on your Web Intelligence server.

These can be found on the server running your WebI installation. Go to ‘Control Panel/Regional and Language Options’. Make sure the values in the ‘Regional Options’ tab are set to the correct locale.

I changed the settings from US to UK and it fixed the date format issues I was having with Web Intelligence on XI.

Incidentally, as far as I can tell there is no sqlsrv.prm for Web Intelligence XI (it is only used for the Deski) - I may be wrong about this though.

MattPhisto (BOB member since 2005-11-28)

6

Hi Debbie,

Dear could you please let us know for ‘WEBI’ where actually we have to change the date format.

Kindly let us know.
:hb:

Thanks in advance

Regards,
Izhar

izhar :pakistan: (BOB member since 2007-07-11)

7

This is untrue, at least for XIr2, the prm file still effects date formats in webi with regards to the SQL generated in the background…

Have a look at this:-

https://bobj-board.org/t/84923

Mak 1 :uk: (BOB member since 2005-01-06)

8

Maks right.

The PRM file relates to the connection server and governs anything that uses the connection server (Webi and Deski).

ABILtd :uk: (BOB member since 2006-02-08)

9

Thanks it worked.

Regards,
Izhar

izhar :pakistan: (BOB member since 2007-07-11)

10

Hi Guys, I hope my explanation can be of use to some folks. :lol:

Problem - Exception: DBD, [CA][Ingres ODBC Driver][Ingres]The requested operation cannot be performed with active transactions.State: 25000" when running manual month end reports in v11.
Problem - When using the generic odbc and odbc3 datasources we have changed the sqlsrv.prm files to dd/mm/yyyy user input. yet when generating a query the mm/dd/yyyy is used
Problem – Exception: DBD, [CA][IngresODBC Driver ][ Ingres] 15 is not a valid month for a date column. State 22008

Solution to dates with generic odbc

The first thing is to understand how it works, below is my understanding which seems to fit what’s happening.

We are using Ingres Net with a generic odbc driver, so the file defining dates used is “odbc.prm”. This file is used for odbc and odbc3 connections.

If using Infoview and opening Deski from this the Infoview browser application the file is located on the server in folder E:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc, note our programs are on drive E.

If however the browser interface is not being used and connection to database is made directly from the PC then the file used is the one on the PC in folder
C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc

Once this file has been obtained the next thing to ascertain is what is the date format used / expected by the database system, in this case Ingres.

For other reasons outside of this document we have specified that the date format is yyyy-mm-dd which is the European format and is specified in Ingres using the Ingres Environment variable II_DATE_FORMAT=ISO. ISO (International Standards Organisation) sets the date formats that Ingres will expect.

For this reason BO must generate the date in the correct format for Ingres.
It does it by reading the odbc.PRM file, line
{\d ‘yyyy-mm-dd’}
I think the other date line is for Java

the \d indicates a date field followed by its format.

Now how does BO know you have entered a date and what format can you enter. If you look at the bottom of the screen when a report is displayed the date format being used is shown e.g. “Last Exec: 24/09/2008 “.
To change it open Desktop Intelligence on the desktop from Infoview and set the date format to English UK, tools/options/language. It may already say English but that will be the American English as it’s a US product. You need to select from unsupported languages the English(United Kingdom) option, and you have to select Forced Migration so that any older reports are forced to use this date format.
Thus you will now be able to use dd/mm/yy or dd/mm/yyyy.

So that concludes my explanation –
Date defined in Deski
Date for database to read defined in .PRM file

Note there is a .PRM file for SQL, OLE DB, Oracle, Access and others.

chriswilby :uk: (BOB member since 2007-05-11)

11

If nothing works, try to modify the bo connection users default language in the database - it worked for me! :mrgreen:

gyoergy@posan.net :austria: (BOB member since 2008-10-20)