Troubles with dates in condition with @prompt

[Former Member]

Hello, Experts!

I've created a condition on universe level with prompt. User needs to input two dates that limit the result of the query.

DB date column has varchar(10) type and the date looks like 'yyyy-mm-dd' without time.

this is my condition:

convert (date, isnull(MoscowWH.dbo.qryDate.TheDate, '1910-01-01')) >= @Prompt('Enter a date from:','D',,Mono,free,persistent) and convert (date, isnull(MoscowWH.dbo.qryDate.TheDate, '1910-01-01')) <= @Prompt('Enter a date from:','D',,Mono,free,persistent)

Also I've created a dimension called Date with the following definition

convert (date, isnull(MoscowWH.dbo.qryDate.TheDate, '1910-01-01'))

. The value of Date dimension looks like 'dd.mm.yyyy' on preview.

But when this condition is deployed the date entered via the @prompt the preview looks like 'dd.mm.yyyy' but the dates sent to the report are in 'mm.dd.yyyy' format.

For example: when i choose dates between 01.09.2011 - 11.10.2011 the dates sent to the report are in range from 09.01.2011 to 10.11.2011

What can be the issue? System locale is RUSSIA.

Edited by: antoxic87 on Oct 11, 2011 3:44 PM

[Former Member · Accepted solution]

Hello,

Can you check what is entry for parameter "USER_INPUT_DATE_FORMAT" in the PRM file?

Thanks,

Vivek

[Former Member · Accepted solution]

file sqlsrv.prm from oledb folder contains this sting:

<Parameter Name="USER_INPUT_DATE_FORMAT">'mm/dd/yyyy HH:m:s'</Parameter>

[Former Member · Accepted solution]

Ok

Now we can follow either way: -

1) Change the value in the PRM itself for <Parameter Name="USER_INPUT_DATE_FORMAT">'mm/dd/yyyy HH:m:s'</Parameter>

to

<Parameter Name="USER_INPUT_DATE_FORMAT">'dd-mm-yyyy HH:m:s'</Parameter>

Before this close all the application and then do this change.

OR

2) if you don't want to change the PRM file since it will affect all your universes, set USER_INPUT_DATE_FORMAT in your universe parameter --> advanced section and give the desired value so it will only be related to this universe only.

Just try and see if it helps you out.

Thanks

Note: - make sure that you are picking up the correct PRM file.

[Former Member · Accepted solution]

The second way doesn't work (( i've created parameter USER_INPUT_DATE_FORMAT and assigned a value like dd-mm-yyyy HH:m:s then exported universe to CMS. but report hasn't any changes even when i've refreshed data.

Edited by: antoxic87 on Oct 12, 2011 11:28 AM

Edited by: antoxic87 on Oct 12, 2011 11:40 AM

[Former Member · Accepted solution]

The first way works perfectly. but it looks like hacking. maybe another way to solve this problem exists...