Preceding zeroes for a date object

[Former Member]

I have a character object called DateofAssessment(char) which displays the data in the following format : 01/01/2009

I have another object called DateofAssessment which is defined as a date with the following select statement defined in the universe:

CONVERT(datetime, @select(DateofAssessment(char)))

With BO XI R2 DateofAssessment returned a date with the preceding zeroes i.e. 01/01/2009, but now we have upgraded to BO XI 3.1 the date is returned as 1/1/2009.

In order to return the preceding zeroes I have tried adding a style to the statement and also tried casting the value and I have got nowhere. Can anyone tell me how I can convert the character field into a date field and retain the preceding zeroes?

Regards

Jason

[Former Member]

Just in case someone else gets the same problem, I have resolved the preceding zeroes issue by changing the ANSI92 indicator (on the parameters tabs in the Universe) to Yes.

Regards

Jason

[Former Member]

Hi,

Unfortunately this problem has come back in a different form.

I have reports that use predefined filter that selects the date and these filters work without problems when running the reports.

However, if the report is edited (or a new report is set up using these filters) an error message is displayed 'An internal error occurred while calling 'processDPCommands' API. (Error: ERR_WIS_30270)'.

The filters can still be used, but the date is in the US format (mm/dd/yy) with no preceding zeroes.

Can anyone tell me where I can change the date format setting? I have tried amending the ANSI92 setting on the Universe parameters and then I tried amending the sql date conversion style to 103 (UK format) on the date object being filtered. Neither change had an impact and I was wondering if there is a global setting that needs to be changed?

Regards

Jason

[Former Member]

Hi Jason,

Try in Connection Server folder (C:\Program files\Bo\Bo versions\win32_x86\dataAccess\connectionServer\sql server) i.e. for each database Bo connection strings were defined in this files in that also check for prm files (what is the date format used there)

In my knowledge Ansi 92 option is for enabling outer joins in BO.

Please ignore this if it is not useful.

Cheers,

Suresh Aluri.

[Former Member]

Hi Suresh,

We have tried changing the sqlsrv.prm and sqlsrv7.prm files in the past and unfortunately it had no impact, but thanks very much for your suggestion.

Does anyone know how I can raise a query with sap to resolve this?

Regards

Jason

[Former Member]

Didier,

Thanks for your response, unfortunately it has no effect. I am using SQL server and I did try using style 103 this morning (as I am in the UK) and this also had no effect.

With regards to the explanation, I do not have access to that help text.

A colleague was wondering if the sqlsrv files are having an impact on this as they were amended for version 3.1, but I cannot find any 'know problems' when browsing the web for information?

Regards

Jason

[Former Member]

Hi,

If you are using SQL Server database (that's I assume) please try the following syntax:

CONVERT(datetime, @select(Class\Object), 101)

Please have a look on this help page:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm

Didier