1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Date diff in oracle

Discussion in 'SQL PL/SQL' started by CM7968, Sep 5, 2012.

  1. CM7968

    CM7968 Guest

    i am working on a simple process time study , calculated from 2 DB date - start and end date:

    select
    24*60*(to_date(start_time,'mm/dd/yyyy hh:mi:ss')-to_date(end_time,'mm/dd/yyyy hh:mi:ss')) as Processtime
    from oracleDB

    i hit an error ORA01858: a non-numeric character was found where a numeric is expected

    any turnaround for this? thanks.
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Please give the exact values you put in your query and provide us your table description with type of each column.
    Also kindly use code tags to make your code more readable
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi,

    Use the query as explained below:

    Code (Text):

    SQL>select sysdate,sysdate-1,(to_date(sysdate,'dd-mm-yyyy hh24:mi:ss')-to_date(sysdate-1,'dd-mm-yyyy hh24:mi:ss'))*24*60 TIME_GAP from dual;
    Selected 1 record

    Result:
    *****
    SYSDATE                 SYSDATE-1           TIME_GAP
    09/05/2012 03:33:46 09/04/2012 03:33:46 43200

     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Interesting example but it doesn't explain why the error was thrown. Such an error indicates that the to_date format string doesn't match the input string. I would suspect that the source date string includes either an AM or PM indicator which is not addressed in the to_date format picture.
     
    Bharat likes this.