Problem in regard with the 'ToDate' Function

[Zeenat]

My problem is regarding Date Function in WebI XI R2,

1. The Report is Migrated from BO 6.5.4 to DeskI XI R2 and than converted the .rep report to .wqy report.
2. Custom SQL is used from the DeskI to WebI

UserResponse function is working fine, but as soon as I am using ToDate Function it gives #Error

=UserResponse([Query 1 with ABCD]; "From Trip Date:"


=ToDate(UserResponse([Query 1 with ABCD]; "From Trip Date:"); "MM/dd/yyyy")

The Complete format I required is, this same format is working for DeskI but for WebI it is not working :

=FormatDate(ToDate(UserResponse ([Query 1 with ABCD] ; "From Trip Date:") ; "MM/dd/yyyy h:mm:ss a") ; "Ddd, MM/dd/yyyy hh:mm a")

The same thing is working for few other reports but in 3 reports I am getting this kind of Problem, and all these 3 reports have Custom SQL required in that.

Does anybody have the idea about it....

[bdiss]

I understand your frustration and I ran into the same issue with Webi dates from user response. My team & I tried a number of different resolutions and the only one we could get to work was to use the Left function and the substring function to break apart the string. Webi adds a time stamp to dates when using the "user response", which seems to be the root cause of the issue.

We have a date object in our universe with values returning "mm/dd/yy", but when we use the user reponse we get a time stamp after the date, which isn't there in the db. We used the "IsDate" function in the report to figure out that Webi doesn't see the user response value as a date even though it is a date in the univese and it is a date when we pull the date back in the select clause. We also tried the "ToDate" and it failed for us as well. You may also want to try bringing the column back in your report and creating variables that return the max / min of the specific column. You will be bypassing the user reponse, but should still be able to get the data you are looking for.

[Zeenat]

i get u but not completed, it would be great if u can send me the code too, so that i can be able to understand it completely...

[bdiss]

Hi Zeenat,

Sorry about the delay. I haven't checked the site for awhile.

Here is the code that we use:
=Left(UserResponse([Data Provider Name];"Prompt Text");
Length(UserResponse([Data Provider Name];"Prompt Text"))-12)

It works great for us because it is dynamic. The time stamp (including spaces) is always 12 characters, whereas the length of the entire string can vary depending on whether the date has 2 digit month / day or 1 digit month / day.

Thanks,

[tech_sk8rboy]

or try this code:

ToDate(Trim (Substr( UserResponse("Enter Start Date:");1;Pos(Concatenation(Trim(UserResponse("Enter Start Date:"));" ");" ")));"M/d/yyyy")

thanks,

[ap.achary]

Thank you very much for the solution provided above as I was also having a similar problem with ToDate Conversions in WebI. This is best work-around.

Achary.

[JohnJustus]

What was the formula did you used? Can you please let me know.
Thanks.