Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Really need expert help ASAP

    Hello everyone again,

    I really need some help as I am crunched for time (then again aren't we all)

    I am using oracle 9.2. with Crystal reports XI. I am using a storedprocedure/package to connect to crystal reports. Now in my where clause of my storedprocedure I have the following:

    Code:
    WHERE Convert_seconds(Epoch_Time) BETWEEN to_date(StartDate, 'YYYY-MM-DD HH24:MI:SS') 
    AND to_date(EndDate, 'YYYY-MM-DD  HH24:MI:SS')
    When I try and run this in crystal reports and use the following
    Startdate = 2008-04-08 00:00:00
    EndDate = 2008-04-08 11:59:59

    I don't get any data returned by I know in the database there is because I put it in

    If I do a Show SQL Query in crystal I get

    Code:
    BEGIN "User_Read"."Test_Report"."R_Test_Report"('Fund Accounting', TO_DATE ('08-04-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), TO_DATE ('08-04-2008 11:59:59', 'DD-MM-YYYY HH24:MI:SS'), :RESULTS_CURSOR); END ;
    So it all looks good to me, but I don't know how to troubleshoot this. I may be missing how crystal an oracle work with respect to format of datetime parameters that are coming from a stored procedure. The time is very important for me here because users may want to check for records per within a certain datetime.

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You are calling the stored procedure with variables of type DATE already, so I don't see a reason why you need the TO_DATE() in the procedure itself:
    Code:
    WHERE Convert_seconds(Epoch_Time) BETWEEN StartDate AND EndDate
    But to be sure you will need to post the full source code of the procedure (at least the definition of the parameters)

    Additionally: hat does convert_seconds do? This is not an Oracle function (AFAIK), so maybe that is converting the epoch_time incorrectly

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Shammat,

    Thanks for the reply. I should have mentioned that I did try
    Code:
    WHERE Convert_seconds(Epoch_Time) BETWEEN StartDate AND EndDate
    removing the to_date but didn't have sucess.

    Moreover, you are looking for the definitions of the epoch conversion function I think is what your asking. The one where I say convert_seconds(Epoch_Time)?

    Here is definitions basically of the function

    Code:
    BEGIN
      Translate_EST:=new_time(TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')+(p_Time/(60*60*24)), 'GMT', 'EST');
      Translate_EDT :=new_time(TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')+(P_Time/(60*60*24)), 'GMT', 'EDT');
    Please let me know if that helps.

    p.s. I am not sure what "hat" is in your sentence "Additionally: hat does..."
    Last edited by MrhelpMe; 04-17-08 at 15:47.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MrhelpMe
    Here is definitions basically of the function
    That is not a definition of the function. But let's assume that is working correctly.

    But the definition of the R_Test_Report function is a lot more important.

    With definition I mean (ideally) the complete source code. But the declaration (the part with CREATE PROCEDURE) will do to find out what datatypes the procedure expects.

    Did you try to remove the to_date() stuff from the where condition as suggested in my answer?

    Quote Originally Posted by MrhelpMe
    p.s. I am not sure what "hat" is in your sentence "Additionally: hat does..."
    That should have been "what"

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    O.k I see what you want, but to first answer whether I tried it without the to_date. Yes I did remove it an I got the same result. Not sure if you saw that in my last post as I edited it.

    So there is really nothing to my create procedure but here it is
    Code:
    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 to_date(StartDate, 'YYYY-MM-DD HH24:MI:SS') 
    AND to_date(EndDate, 'YYYY-MM-DD  HH24:MI:SS');
      END;
    END;

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MrhelpMe
    O.k I see what you want, but to first answer whether I tried it without the to_date. Yes I did remove it an I got the same result. Not sure if you saw that in my last post as I edited it.
    Ah. Yes, I did miss that one

    So from the source code the to_date() is definitely not necessary, so you should remove it.

    One thing I just noticed: you are passing 11:59:59 for the time portion.
    Do you want to select the whole day? If so, your date format is wrong as it specifies 24 hour format. The time that you pass will select everything up to noon, not until midnight.

    What do you get, when you run the following manually e.g. in SQL*Plus (or any other SQL tool)
    Code:
    SELECT  Ticket_ID AS TicketID,
            Convert_seconds(Epoch_Time) AS CreateTime
    FROM table
    WHERE Convert_seconds(Epoch_Time) 
       BETWEEN to_date('2008-04-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
    AND to_date('2008-04-08 11:59:59', 'YYYY-MM-DD  HH24:MI:SS');
    If you do want the full day returned, then you have to change 11:59:59 to 23:59:59.

  7. #7
    Join Date
    Apr 2006
    Posts
    140
    shammat,

    In trying to fix the code, I must have left the time portion in What I want to do is allow the user to pick the date and time. So really the time portion should not have been hardcoded in there. Now if I remove the to_date, I am left with what you said but that is not working. Any other ideas?

    So if I do

    Code:
    SELECT  Ticket_ID AS TicketID,
            Convert_seconds(Epoch_Time) AS CreateTime
    FROM table
    WHERE Convert_seconds(Epoch_Time) 
       BETWEEN StartDate AND EndDate;

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What does the CONVERT_SECONDS function do? If I read the name right, it should convert a date to an integer*? Can an arbitrary integer be between an arbitrary pair of dates? Sometimes, but you have to be pretty lucky.


    * Warning! Huge assumption here

  9. #9
    Join Date
    Apr 2006
    Posts
    140
    MCrowley,

    Actually it is the other way around. The function takes a EPOCH Integer(the number of seconds since Jan. 1 1970 and converts this to a date accounting for daylights savings time.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MrhelpMe
    I am left with what you said but that is not working. Any other ideas?
    What do you get if you run the statement the way I wrote it?

  11. #11
    Join Date
    Apr 2006
    Posts
    140
    shammat,

    I am not sure what your referring to. I didn't see you write anything but I may have misunderstood. Please refer to the code that you wrote so that I can copy and paste this and try it out. Thanks.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Run this in SQL*Plus:
    Code:
    SELECT  Ticket_ID AS TicketID,
            Convert_seconds(Epoch_Time) AS CreateTime
    FROM table
    WHERE Convert_seconds(Epoch_Time) 
       BETWEEN to_date('2008-04-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
           AND to_date('2008-04-08 11:59:59', 'YYYY-MM-DD HH24:MI:SS');
    And let us know what it returns.

  13. #13
    Join Date
    Apr 2006
    Posts
    140
    Shammat,

    When I run your posted code in Oracle SQL Plus, I get no rows selectedwhich shouldn't be the case.

    If I run the following on the database table I have 2 records that should be returned in that date range

    Code:
    select Ticket_id,
           to_char(Convert_second(epcoh_time), 'DD-MON-YYYY HH:MI:SS AM')
    
    from table;
    Code:
    TIC000031234567	08-APR-2008 02:02:27 PM
    TIC000000000005	08-APR-2008 02:50:32 PM

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by shammat
    Run this in SQL*Plus:
    Code:
    SELECT  Ticket_ID AS TicketID,
            Convert_seconds(Epoch_Time) AS CreateTime
    FROM table
    WHERE Convert_seconds(Epoch_Time) 
       BETWEEN to_date('2008-04-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
           AND to_date('2008-04-08 11:59:59', 'YYYY-MM-DD HH24:MI:SS');
    And let us know what it returns.
    And let us know what it returns.
    And let us know what it returns.
    And let us know what it returns.
    And let us know what it returns.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by MrhelpMe
    Shammat,

    When I run your posted code in Oracle SQL Plus, I get no rows selectedwhich shouldn't be the case.

    If I run the following on the database table I have 2 records that should be returned in that date range

    Code:
    select Ticket_id,
           to_char(Convert_second(epcoh_time), 'DD-MON-YYYY HH:MI:SS AM')
    
    from table;
    Code:
    TIC000031234567    08-APR-2008 02:02:27 PM
    TIC000000000005    08-APR-2008 02:50:32 PM
    Your rows are in the afternoon and you are searching only in the morning. Try

    Code:
    SELECT  Ticket_ID AS TicketID,
            Convert_seconds(Epoch_Time) AS CreateTime
    FROM table
    WHERE Convert_seconds(Epoch_Time) 
       BETWEEN to_date('2008-04-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
           AND to_date('2008-04-08 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •