Date Format from the UserResponse()

system · June 21, 2010, 5:10pm

Hello,

="Date Range:   "+ UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:")+ "To"+ UserResponse([Query 1]; "Enter To Date [MM/DD/YYYY]:")

Above is my code and its working fine to get the user response to the date range prompt…But I need the user entered date in Month - Year(Eg: April - 2009 To May - 2009) date range

Now it is showing as 01/01/2009 To 31/01/2009 format…

I tried with Todate() and FormatDate(), but it throws #Error…

please help

Thank You
Nikki

Nikki (BOB member since 2010-04-20)

system · June 21, 2010, 6:56pm

UserResponse returns character only.

FormatDate is the correct thing to do. But, without seeing what you have tried…

Steve Krandel (BOB member since 2002-06-25)

system · June 21, 2010, 7:53pm

Hi Steve,
Following is the code…It gives # Error

="Date Range:   "+FormatDate(ToDate(UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:");"dd/mm/YYYY");"mm/yyyy")

Thank You
Nikki

Nikki (BOB member since 2010-04-20)

system · June 21, 2010, 8:30pm

Don’t you see the inconsistency here?

I would first make sure my formats match. You’re asking the user to enter MM/DD and then telling the ToDate() function the format is DD/MM.

Dave Rathbun (BOB member since 2002-06-06)

system · June 21, 2010, 9:04pm

Sorry for that …still its not working

="Date Range: "+FormatDate(ToDate(UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:");"mm/dd/YYYY");"mm/yyyy")

Thanks
Nikki

Nikki (BOB member since 2010-04-20)

system · June 22, 2010, 1:31am

Also within Webi, month and only month needs to be in capital letters if you have it in lower case as you have in your format clause, its treated as minutes. You also shouldn’t have capital YYYY in the format. This too needs to be lower case.
The text in your prompt value is irrelevant and makes no difference to what you enter in the prompt as you can’t enter upper case digits (or at least you can’t in Australia ). The text is merely a note to the user that this is the format that the query is expecting.

So it should be:

“MM/dd/yyyy”
and
“MM/yyyy”

Please also note that if your prompt allows the use of time to also be added your code will still error as it is receiving more than 10 bytes, so you can handle that a couple of ways.

Substring the UserResponse to only select the first 10 char.
Add in a If statement to check the length of the UserResponse. If its more than 10 char, put an addition format string in that handles the time portion of the date.

plessiusa (BOB member since 2004-03-22)

system · June 22, 2010, 1:18pm

Thank You for you reply Plessiusa,

Still its not working …

= "Date Range: "+FormatDate(ToDate(substr("UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:")";1;5);"MM/dd/yyyy");"MM/yyyy")

Even then I changed it to the MM, still its showing #Error.

I am tried with Stbstr() its not getting parsed.Please correct the code.

Thank You
Nikki

Nikki (BOB member since 2010-04-20)

system · June 22, 2010, 2:07pm

Fix your todate parameter. YOur users are entering the data as dd/MM/yyyy. That’s what should be inte he 2nd parameter of the todate function.

You need to bereak things into smaller pieces and get it working 1 step at a time. Then you can put it all together.

Steve Krandel (BOB member since 2002-06-25)

system · June 22, 2010, 5:27pm

Steve thank you for reply…

I am using the same as the user enter format in todate function which is (month/date/year)

Nikki

Nikki (BOB member since 2010-04-20)

system · June 22, 2010, 8:38pm

Ok, Try this.

=FormatDate(ToDate(Substr(UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:");1;10);"MM/dd/yyyy");"MM/yyyy")

or as Steve said earlier, break it down, start with one part and build it up

=UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:")

then

=Substr(UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:");1;10)

then

=ToDate(Substr(UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:");1;10);"MM/dd/yyyy")

Finally add the Date Range: description back into it.

="Date Range: "+FormatDate(ToDate(Substr(UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:");1;10);"MM/dd/yyyy");"MM/yyyy")

I’ll be surprised if the above doesn’t work though.

You’ll also notice in your code that you have a " before UserResponse and before the 1;5 that shouldn’t be there and also that your substr is 1;5, it needs to be 1;10 for the ToDate.

plessiusa (BOB member since 2004-03-22)

system · June 23, 2010, 1:39pm

Hi Plessiusa thank you for your reply…

its working fine till substr().The problem is with Todate() it get parsed but showes #Error in the cell…I am using Teradata(not sure it makes any difference as its at the report level format)

all I am looking is to get Month- year(April - 2009 for example) in the
date range

Thank You
Nikki

=ToDate(Substr(UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:");1;10); "MM/yyyy")

Nikki (BOB member since 2010-04-20)

system · June 23, 2010, 1:55pm

Todate turns it to a date.

=ToDate(Substr(UserResponse([Query 1]; “Enter From Date [MM/DD/YYYY]:”);1;10); “MM/dd/yyyy”)

Once you have that, then you can format it.

Steve Krandel (BOB member since 2002-06-25)

system · June 23, 2010, 1:58pm

What to say? You didn’t follow plessiusa’s code:

=FormatDate(ToDate(Substr(UserResponse([Query 1]; "Enter From Date [MM/DD/YYYY]:");1;10);"MM/dd/yyyy");"MMM yyyy")

And also check that userresponse always returns value like 06/06/2010 12:00:00 otherwise if it’s 6/6/2010 12:00:00, 6/16/2010 12:00:00 then you might need to handle substring function differently,substr([object];1;length([object])-9) from right.

Prashant Purohit (BOB member since 2009-02-18)