BusinessObjects Board

ToDate Function - User Response - DaysBetween

building reports webintelligence
#1

I’m creating a variable that has the following formula:
=ToDate(UserResponse([Marketing - Common Dimensions]; “Enter Termination Date (End):”); “MM/dd/yyyy”).

I’m trying to take the User Prompt response and converting it to a date so that I can use this field as part of another variable which will use the DaysBetween function.

When I place the variable in the report in its own cell, I keep getting #ERROR as the result.

Can someone tell me what is wrong with the formula. I am working on a SQL Server database.

PGO_AIM :us: (BOB member since 2006-11-10)

Date Prompt in Webi Calendar.
Can not get Days Between
Dates and SQL Server
#2

Please try the following:

Replace the variable with this this:
=ToDate(UserResponse([Marketing - Common Dimensions]; “Enter Termination Date (End):”); “”)

Save the report

Reopen the report

Hopefully it should work now.

Regards,
Rim Geurts

RGeurts :uk: (BOB member since 2006-12-29)

#3

If the above doesn’t work, give this a shot:

=ToDate(UserResponse(“Enter Termination Date (End):”); “MM/dd/yyyy hh:mm:ss A”).

rimpa :india: (BOB member since 2008-04-14)

#4

I left the date format “” as suggested and this worked. Thank you, your suggestions have worked. Its appreciated.

PGO_AIM :us: (BOB member since 2006-11-10)

#5

Rim Geurts, thank you soooo much! your solution worked. i don’t know why or how (which annoys me) but removing the MM/dd/yyyy worked.
:+1:

twenty5 (BOB member since 2008-01-22)

#6

I was having the same problem. rimpa’s solution worked and here is why:

The UserResponse function gives you back text. When it is a date it not only gives you the MM/dd/yyy it also gives you the hours, minutes seconds and AM or PM. Generally you will get 12:00:00 AM if there is no real time.

So in order to change this text of “11/13/2008 12:00:00 AM” into just 11/13/2008 you need to use this formula:

ToDate(UserResponse(“Enter Termination Date (End):”); “MM/dd/yyyy hh:mm:ss A”)

The key is using the full date/time format since that is what is in the text returned by the UserResponse function. If you don’t you just get an error.

Thanks for pointing me in the right direction!

:smiley:

bmciance :us: (BOB member since 2002-08-16)

#7

Not one of these two solutions worked for me :frowning:

I have a varchar(max) field and the data stored in here as well as my reporting attempts are follows:

=ToDate([Date]; “Mon dd yyyy hh:mi:ss:mmmAM”)

returns #ERROR

di652 (BOB member since 2005-02-24)

#8

=ToDate([Date]; “Mon dd yyyy hh:mm:ss:mmm AM”)
There should be a space. And for minute, you have to use mm. Try it out.

Jansi :india: (BOB member since 2008-05-12)

#9

Hey one more thing to add. Have only A and not AM in your code.

Jansi :india: (BOB member since 2008-05-12)

#10

Thanks, but it didn’t work. I still got the error message #ERROR

di652 (BOB member since 2005-02-24)

#11

brendanmc

I tried to thank you since your remedy worked like a charm but unfortunately the request was locked!

I do understand about cross posts but I have seen others reply and ask same questions.

Anyway, thanks again.

di652 (BOB member since 2005-02-24)