Date formats within Oracle 10G

[ohsillyme]

Hello...
Help!

(Using Business Objects Full Client 6.52 and Oracle 10G - REAL-TIME data)

I am having trouble with my prompt. I keep getting 'no data to fetch'.

(ORDERS.ORDER_DT Between @Prompt('Start Date','a',,mono,free) AND @Prompt('END Date','a',,mono,free) )

I can get it to work 'sometimes' using this format: '21-12-2007' but it doesn't always work? Is there a glitch? Am I doing something wrong?

I know Oracle stores it's dates this way: '21-DEC-07 00:00:00', but of course that date format doesn't work if I use it within a prompt.

(I would prefer to truncate the date so that I don't have to plug in the time, but that adds 2 minutes to my query!!! Is there a more efficient way to do this and ensure that I am seeing a complete day?)

Any assistance with this issues would be GREATLY APPRECIATED!

Thanks!

 

[skrandel]

Try changeing the 'a' in your @prompt to 'D'. It may not help, but it might. 'a' is for alphanumeric entry, while 'D' is for Date.

Since BO automatically puts the time in at the end of a date string, this shouldn't hurt your indexing.

Steve Krandel
Intuit

 

[ohsillyme]

Hi Steve!

Thanks for the response. I started off with 'D' and moved to 'A' because I was getting the 'no data to fetch' message. I don't know what format it expects. I've tried every possible combination I can think of. At this point, I am assuming that it just doesn't like the prompt altogether and possibly no format will do.

btw...I forgot to mention that I was using straight SQL. I am not using a universe to create this report.

 

[blom0344]

Oracle uses datetime types to store both real dates and time values. The NLS_FORMAT is by default 'dd-mon-yyyy', but theoraticaly this could be different in your case.
Did you test the setting?

No idea if @prompt is supported with freehand SQL. @variable does work (used it before). Try to feed it:

'24-DEC-2007 00:00:00' as inputformat

Ties Blom

 

[ohsillyme]

Hi blom0344 -

I have also tried that format. What I hadn't tried was the @variable. @Prompt has worked in the past for me using other types of values and simple dates, but not this time. I just tried the '@variable', but no 'workie'. I get the same response.

Thanks for your response!

 

[blom0344]

Take it step by step:

Does the following give back results:

SELECT ...
FROM ....
WHERE..
ORDERS.ORDER_DT BETWEEN '21-DEC-07 00:00:00' AND
'22-DEC-07 00:00:00'

So, do not use @prompt / @variable , but try a fixed expression in the where clause to test first..

Ties Blom

 

[ohsillyme]

Hi..

Yes, it does work if I hard-code it, but since the users do not have SQL access, I was trying to empower them a bit.

thx!

 

[ohsillyme]

Oh one more thing. I have added other prompts to this same query. The input values of these are not dates, but they work just fine. I have removed them and hard-coded their values temporarily to ensure they don't conflict with 'my PRECIOUS' date prompt. I wonder if there is some kind of glitch with Oracle 10G and BO 6.5 date prompts?? That would ....!

 

[WLW]

How about wrapping your @prompt in a date()- like (ORDERS.ORDER_DT Between date(@Prompt('Start Date','a',,mono,free)) AND date(@Prompt('END Date','a',,mono,free)) )

 

[uszaty1973]

Please use todate and alphanumeric prompt. You can also should try to fix date setings eg. nls_date parameters or other. Also BO has settings on BO server or desktop or universe level for fixing date format problems.