Hello experts,

I am not having any luck on other forums and hopefully someone has the patiece and knowledge here. I am using Oracle 9.2 (stored procedures and packages) and tapping crystal report xi to a stored procedure. I am trying to pass Date (which includes datetime in oracle) parameters from the stored procedure to the crystal report. I need to allow the user to be able to choose a date and time.

My procedure is as follows:

CREATE OR REPLACE PACKAGE BODY Test_Report
AS
 PROCEDURE R_Test_Report (GroupSelect IN VARCHAR2,--use Fund Acct
                                      StartDate IN DATE,
                                      EndDate IN DATE,
                                      results_cursor IN OUT CURSOR_TYPE)
 IS
 BEGIN
   OPEN results_cursor FOR 
   SELECT 
           Ticket_ID AS TicketID,
           Convert_seconds(Epoch_Time) AS CreateTime
   FROM table
    WHERE Convert_seconds(Epoch_Time) BETWEEN StartDate 
AND EndDate;
 END;
END;

When I try and run this in crystal reports and enter a start date and end date, it doesn't seem to be retunring all records within the datetime range, which it should. Does anybody see anything wrong?
Please help on this. Thanks.

Have you tried calling the procedure from oSql or Enterprise Manager? Do you get the same results?

I'd guess you're storing the date and time, but only passing in the date through the parameters? Are you missing anything after midnight on end date?

ForrestCroce,

Yes I get the same results. Not sure what you mean am I missing anything after midnight?

Would you happen to have simple code like mine that works with 2 date parameters. I just want to see what is different. Is it Oracle or Crystal that is the problem? Thanks again.

What rows are missing? You said "it doesn't seem to be retunring all records within the datetime range" ... so which aren't being returned?

If you ask for 1/1/08 to 3/1/08, but store 3/1/08 1:15:00.00 AM, that row doesn't fall between the two date time values. It's on the last day, but if you aren't passing in the time part with your parameters, midnight is defaulted, and 1:15 am is after midnight.

My guess is that's what's going on.

ForrestCroce,

I just figured it out. My application is using a 24 hour clocl and by putting in the values I choose, that would mean it is looking for everything in the am which isn't right. After making the approipriate changes to the time all is well. Thanks again or your help.