Date format in Universe as prompt

[Former Member]

I have a feild curr_dt_time in database and I need to create prompt with date range so that user can select a date range in the report.

I have created two prompts in Universe

Start Date

End Date

(@Select(DateTable\Current Date Time) IN to_date(@Prompt('Select Start Date','D','DateTable\Current Date Time',free,Persistent),'MM-DD-YYYY'))

(@Select(DateTable\Current Date Time) IN to_date(@Prompt('Select End Date','D','DateTable\Current Date Time',free,Persistent),'MM-DD-YYYY'))

which parsed successfuly.

I used to_date function because user has to see only date not time.

When I use this prompts in my report level, getting below error.

Error ORA-08130:date format picture ends before converting entire input string

Please share your thougths and let me know where I am going wrong.

Also, I need to select the date range values in my report. How do I do that.

[Former Member · Accepted solution]

Hi Ven,

I had the same issue and found that doing a "TO_DATE" does not actually work in this case. I unpacked the entire string using "SUBSTRING" and then converted it to a date.

You could try to do somthing like this... to_date(substring(@prompt(......),1,10),'MM/DD/YYYY')

Or try just doing the substring and pull the object into a report to see what the format looks like and then unpack it.

Hope this helps

Jacques

[Former Member · Accepted solution]

Thanks Jacques. It worked.

I could able to select prompts for Start Date and End Date.

I need some more help. How do I capture the date range in my report.

I mean, I need to capture the date range(Start Date and End Date in prompts) in on field called Date.

suppose I select start date as 10/06/08 and End date as 10/08/08, in the report under date field three

records should be displayed 10/06/08, 10/07/08, 10/08/08

How do I get it..

Thanks,

Vrrm

[Former Member · Accepted solution]

Hi Ven,

You can use "BETWEEN"... i.e DATE BETWEEN @prompt(begin date) AND @prompt(end date)

Or

DATE >= @prompt(begin date) and DATE <= @prompt(end date)

Regards

Jacques

[Former Member · Accepted solution]

Hi

Giving error when I am trying to create variable in report as below

=[Lst Updttm] >= @prompt(Start Date) and [Lst Updttm] <= @prompt(End Date)

[Lst Updttm] is the date field I from which I created two prompts Start Date and End Date in Universe..

Thanks,

Vrrm

[Former Member · Accepted solution]

Hi Ven,

The syntax I gave is abreviated and is to be used in the universe.

If you need to do this prompting in at report level, then the query panel has all the functions available in a nice drag and drop fashion.

Regards

Jacques