Date error WIS 10901

[Former Member]

Hi,

I'm facing an issue with dates being compiled incorrectly in an American format in the SQL code of Webi queries.

This produces the error below because the databases want dates in the English UK format:

Database Error: [Microsoft SQL Server Native Client 10.0] : the conversion of a char data type to a datetime data type resulted in an out of range datetime value (IES 10901) (WIS 10901)

My system is BI Platform 4.1 SP1 on Windows 2008 R2 Standard.

The server o/s is set to English UK, and UK date formats.

The browser (IE 10) is set to render pages in UK English.

The user preferences in BI Launchapd are set to English and UK English respectively.

The unv universes involved have been exported from Designer where the language options were set to English UK and 'migration forced'.

The SQL usernames involved in the connections are all using British English.

When I run a report if I include a date filter, the calendar icon in the query panel puts dates into the filter in an American format which is incorrect, but even if they are input in the correct UK English format (dd/MM/yyyy HH:mm:ss) then the SQL is generated in the American format again (MM/dd/yyyy HH:mm:ss) and the database causes the error.

The connections here are all OLEDB and I have tried adding the old USER_INPUT_DATE_FORMAT parameter to the sqlsrv.prm file in the ...dataAccess\connectionServer\oledb folder.

Adding this parameter along with the UK English date format enables the queries to run in the IDT installed on the server when I convert th eunv to unx (even if we switch between local and server middleware), but no where else.The unx then still shows the same behaviour as the unv when used in a query in webi.

Webi in the browser and Webi Rich Client do not take any notice of this parameter even after repeated connection server restarts.

This 4.1 system has recently been migrated from 3.1 SP5 where everything was working fine - we are using the same settings as far as we can given the differences in the products and files.

Does anyone have any other ideas?

thanks

Keith

[Former Member · Accepted solution]

the correct answer turned out to be that in BI4.1 there are extra sqlsrv.prm files deeper into the connectionserver directory in ...\oledb\extensions\legacy.

In my installation this prm file was actually missing altogether, but once replaced from another installation, I could amend the date format in this file and the dates were then compiled correctly in the SQL of queries.

Thanks for everyone's help.

[Former Member · Accepted solution]

Hi Keith,

Can you please help me ?

In my env as well prm files are misisng under legacy folder.

Can you please confirm, how many prm files should be there under legacy folder?

And what changes I have to do in the files.

I have copied sqlsrv.prm file from qt to legacy folder but no luck

Please confirm what needs to be set for database date format and user input date format ?

Thanks in advance

[Former Member · Accepted solution]

Hi Priyanka,

After placing the prm files did u restart your severs, If not restart the server and try again.

Regards

Kumar

[Former Member · Accepted solution]

Can you please confirm use of prm files?

will it make changes at database level ONLY in the report query or it will show the dd/MM/yyyy HH:mm:ss date format in the user prompt while refreshing the report ?

Please confirm

[Former Member]

BusinessObjects displays the dates in the format chosen in the Windows Regional Settings. However, in the condition object, you must enter the dates in the same format as the database or in the parameter "inputdateformat" in the *.sbo file.

In your case, Microsoft SQL Server expects a date in the Amercian format

Please refer to SAP Note 1204251 for resolution

[Former Member]

Jessie - when I try to access that SAP note, it says that its not been released.  Any idea why?

[former_member195290]

Hi Keith,

There are few things you can do to check whether your Data Format is taken as per your need or not, I also have the same scenario in one of my Report where report filter was showing MM/dd/yyyy format but my universe has the dd/MM/yyyy format.

First check the preferences in BI Launchpad >> Locales and Time Zone- is it set according to your needs.

Put the data in Date Filter and check the query script what is coming there in where clause of the query.

Also check the Object Properties in Universe for whats the date format set there.

Your Database used is MS SQL Server check the database parameters set for date.

Regards,

Javed

[Former Member]

Have you checked the Region Settings Properies dialog box in the Server's Windows Control Panel? Might be set to US format even though the OS is UK

If that's not it, then within the advanced tab of object properties in XI3.1 you had the Database Format section there.You may still have the same option where you can enter 'dd/mm/yyyy' (lower case mm because it's talking to the database rather than formatting minutes/months)

Failing that, I'd raise a ticket.

Regards,

Mark

[Former Member]

Have you verified the display format of the object from the universe is correct  by right click on universe object > Edit Display format ?

[Former Member]

Hi Mark,

the server region settings are correct.

If I put a format in the advanced tab of the object, then seemingly this is used by the system (as I can see the date in the SQL is now differently formatted (no hours and minutes)) but the days and months are still reversed.

The one thing that's bugging me most is why I can get it to work in the IDT by adding the sqlsrv.prm parameter, but webi doesn't pick up this change.

thanks

Keith

[Former Member]

Keith,

Where have you made the prm changes?

Have you just made them on your PC to get IDT to work or on the server as well?

If you haven't changed the server settings, I'd give that a try.

[arijit_das]

what is the preferred viewing locale in launchpad preferences for the user ?

[Former Member]

Hi Arijit,

the setting is English (United Kingdom)

thanks

Keith