Results 1 to 6 of 6

Thread: Date Difference

  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Date Difference

    Hello

    I was wondering if you could help me see where I am going wrong and am not able to calculate the difference between 2 fields

    SELECT
    (TO_DATE(TO_CHAR(OP_DATE, 'DD-MON-YYYY') || ' ' || OP_END , 'DD-MON-YYYY HH24:MI') -

    TO_DATE(TO_CHAR(OP_DATE, 'DD-MON-YYYY') || ' ' || ANAES_START, 'DD-MON-YYYY HH24:MI')) AS TEST
    FROM THEATRES


    OP_DATE is a date field and ANAES_START and OP_END are char fields

    Any help would be greatly appreciated

    Thanks

  2. #2
    Join Date
    Dec 2011
    Posts
    82
    P.s The error message I get is:



    Error: ORA-01406: fetched column value was truncated
    (State1000, Native Code: 57E)

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Are you sure that the OP_END and ANAES_START only contain a time stamp of the format 18:43? Try the following select

    SELECT op_date,op_end,anaes_start from THEATRES;

    and see what they look like.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2011
    Posts
    82
    Hello,

    Thanks for your reply

    Yes the OP_END and ANAES_START are in the format 18:43

    I forgot to mention that I need the difference in time in minutes

    Any ideas?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> DESC THEATRES


    SELECT op_date,op_end,anaes_start from THEATRES;

    COPY & PASTE the SQL & results
    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.

  6. #6
    Join Date
    Feb 2005
    Posts
    57
    what do you get when you
    Code:
    SELECT
    (TO_CHAR(OP_DATE, 'DD-MON-YYYY') || ' ' || OP_END , 'DD-MON-YYYY HH24:MI')
    ,(TO_CHAR(OP_DATE, 'DD-MON-YYYY') || ' ' || ANAES_START, 'DD-MON-YYYY HH24:MI')
    FROM THEATRES

Posting Permissions

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