need help with date parametres

[4programming]

Hello once again experts,

I am in desparate need of a solution. I am using Oracle 9.2 and have created a package/stored procedure that I tap into crystal Report XI. This procedure accepts 2 parameters: StartDate and EndDate. Here is my very simple code:

CREATE OR REPLACE PACKAGE BODY Test_Report
AS
PROCEDURE R_Test_Report (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;

If I run this in crystal with the following parameter values

StartDate = 2008-04-08 00:00:00
EndDate = 2008-04-08 11:59:59

I don't get any data returned, however I know there is data in the database for those dates as I put it there
Just to show you

select Ticket_id_, 
          to_char(Convert_seconds(Epoch_Time), 'DD-MON-YYYY HH:MI:SS AM')
FROM table;
 
TIC000000000024 08-APR-2008 02:02:27 PM
TIC000000000065 08-APR-2008 02:50:32 PM

I need to be able to allow the user to choose the date and time as he or she may just want to look at a record at a particular minute, hour etc.

Any ideas? If it is not possible in oracle, is there a work around in crystal that I can use? I don't see what I am asking for as complicated but it could be the way crystal reports XI works with Oracle.

Thanks again

 

[IanWaterman]

What have you put in the select expert of the report.

Ian

 

[CR85user]

You may need to specify date() as the field is currently datetime format.

date({yourtable.datetime}) >= {?Startdate} and
date({yourtable.datetime}) =< {?Enddate}

as part of your selection criteria.

'J