Date format in webi userresponse()

[former_member214617]

Hi guys

i have @prompt created in universe to capture the date entered by the user.. but the problem i am getting is that while displaying the date selected by the user is showing very weird thing.. when the date selected is 03/04/2014 then in webi it is showing in mm/dd/yyyy format and when date is selected 25/04/2014 then it shows in dd/mm/yyyy format which is correct..

i have tried using formatdate(userresponse(); "dd/MM/yyyy") ---- no success

tried making changes in preferences as well ---- no success

tried changing the format on universe level by right clicking the object-> object format->date/time and added new format dd/MM/yyyy --- no success

can you help me what needs to be done to achieve same..

regards

[saurabh_sonawane]

HI,

Can you try this,

instead of breaking date into a hug formula?

try to understand what is the user-response give and output (user-response always return string )

step 1 enter date in the prompt and check what is the user response variable has a value.

if the value = 25/12/2018

and we have to use below formula

formatdate( todate(userresponse("date");"dd/MM/yyyy");"MM/dd/yyyy");

if you get a value as = 12/25/2018

then use below formula

formatdate( todate(userresponse("date");"MM/dd/yyyy");"MM/dd/yyyy");

[Former Member]

Leo,

Break it down into two steps.

See what your UserResponse string looks like first, based on a few runs of the report.

Once you have established which format it is (date first or month first) then build your ToDate formula accordingly - please note that you will need to manage your string format:

dd = 03

d = 3

dd = 14

d = 14

MM = 12

M = 12

MM = 04

M = 4

Note that you may need to chop off the time component of your string if there is one. To do this, susbtring the user response object (let's call it Input Date) first as:

=Substring([Input Date];1;Pos([Input Date];" "))

So your final object of data type Date would be:

=ToDate([Input Date];"whatever your date format is here")

Or

=ToDate(Substring([Input Date];1;Pos([Input Date];" "));"whatever your date format is here")

[ivukcevic]

Hi. Thanks for the formula but in my case it only works if added -1 to Pos():

ToDate(Substring([Input Date];1;Pos([Input Date];" ") - 1); "MM/dd/yyyy")

[Former Member]

Leo,

[Date] = UserResponse("Prompt Text")

Try  this variable,

= formatdate( todate([Date];"dd/MM/yyyy");"MM/dd/yyyy")

[Former Member]

Hi,

SInce 2 years, I think you found a solution to your issue, I have the same.

I resolved with this code : If a user change his "preference" it's work  too using GetContentLocale function

=If(GetContentLocale()= "en_US";ToDate(Left(UserResponse("@Prompt");Pos(UserResponse("@Prompt");" ")-1);"M/d/yyyy");ToDate(Left(UserResponse("@Prompt");Pos(UserResponse("@Prompt");" ")-1);"dd/MM/yyyy"))

with @Prompt your Prompt.

I hope this code can help other people...

[former_member182342]

Hi leo dec,

Check below formula

As UserResponse return string value.

A= UserResponse("Prompt Text")

B=formatdate(todate(A;"MM/dd/yyyy");"dd/MM/yyyy")

Regards,

Anish

[former_member214617]

Hi

Thanks for the reply... little clarification...

created the object using @prompt in universe as date data type @Prompt('Enter the Week End Date ','D',,,)

In webi report selected the universe objects in query and showing in report but in report when the day is between 01 and 12 then it is showing in MM/dd/yyyy format but when day selected after 13th then it shows in dd/MM/yyyy format which is correct...

tried formatdate(object_nm, "dd/MM/yyyy") --- no success

formatdate(todate(object_nm, "MM/dd/yyyy");"dd/MM/yyyy") -- it corrects the format when day selected between 01 and 12 but gives an error when day selected after 13

regards

[arijit_das]

formatdate(userresponse(); "dd/MM/yyyy") will throw you error because formatdate function takes a date as input parameter where userresponse returns a string.

Also, userresponse syntax here is wrong. Correct syntax: =UserResponse("Prompt Text")

What does the formula =UserResponse("Prompt Text") return ?