Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Convert a SUM to Time Format

    Hello,

    I was wondering if someone could kindly help me

    I have written this piece of SQL below, but the results are returned in the following format: 0.000694444444444444

    What I really need is the result to be returned in a time format I.e. 00:01:00

    SUM(TO_DATE(TO_CHAR(OP_DATE, 'DD-MON-YYYY') || ' ' || ANAES_START, 'DD-MON-YYYY HH24:MI') - TO_DATE(TO_CHAR(OP_DATE, 'DD-MON-YYYY') || ' ' || ANAES_ARRIVE, 'DD-MON-YYYY HH24:MI'))

    I am unsure where to put the time format

    Can anyone help me?

    Many Thanks
    Helen

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A similar problem has been discussed just a few days ago.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone help me?
    Is below what you desire?

    Code:
      1* select (to_date('2012-03-27 09:30:00','YYYY-MM-DD HH24:MI:SS')-TO_timestamp('2012-03-27 09:29:00','YYYY-MM-DD HH24:MI:SS'))  FROM DUAL
    10:25:11 SQL> /
    
    (TO_DATE('2012-03-2709:30:00','YYYY-MM-DDHH24:MI:SS')-TO_TIMESTAMP('2012-03
    ---------------------------------------------------------------------------
    +000000000 00:01:00.000000000
    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.

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

    Thank you for your responses.

    I am very new to Oracle and unfamiliar with most of oracle

    In the TO_TIMESTAMP post, you kindly showed me the syntex, which is a good start for me

    However I need to put the Discharge Date which is in date format and concatenate with the Discharge Time field which is in a CHAR format. Then subract the Admission Date and Admission Time from the discharge date and time

    The code I have written is:

    TO_DATE(DISCHARGE_DATE, 'YYYY-MM-DD HH24:MIS') || ' ' || TO_TIMESTAMP(DISCHARGE_HOUR, 'YYYY-MM-DD HH24:MIS'))

    - TO_DATE(ADMIT_DATE, 'YYYY-MM-DD HH24:MIS') || ' ' || TO_TIMESTAMP(ADMIT_HOUR, 'YYYY-MM-DD HH24:MIS'))


    I was just unsure in the code you posted to me, where to put both the discharge date and then the discharge time

    Can you provide any further assistance

    Kind Regards
    Helen

  5. #5
    Join Date
    Dec 2011
    Posts
    82
    My colleague has helped me with what I needed, but thought I would post it on here for future use

    SELECT
    admit_date,
    admit_hour,
    discharge_date,
    discharge_hour,
    TRUNC((discharge_date + discharge_hour/24) - (admit_date + admit_hour/24))
    || ':' || TO_CHAR(TRUNC(24 * MOD((discharge_date + discharge_hour/24) - (admit_date + admit_hour/24), 1)), 'FM09') AS days_and_hours_elapsed

    Hope someone else finds it as useful as me

    Thanks
    Helen

Posting Permissions

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