Results 1 to 4 of 4

Thread: time difference

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: time difference

    HI All,

    I have 2 fields in mine control table which keeps track of the start
    and end of job.

    Data is somewhat like this


    SQL> select start_dt,end_dt from ocean.proc_bch_job_sts where prg_nm
    ='UOQ0150' and rownum < 4;

    START_DT END_DT
    --------- ---------
    02-AUG-01 02-AUG-01
    04-AUG-01 04-AUG-01
    06-AUG-01 06-AUG-01

    3 rows selected.

    SQL> select to_char(start_dt,'dd mm yy hh:mi:ss'),to_char(end_dt,'dd
    mm
    yy hh:mi:ss') from ocean.proc_bch_job_sts where prg_nm='UOQ0150' and
    rownum < 4;

    TO_CHAR(START_DT,' TO_CHAR(END_DT,'DD
    ------------------ ------------------
    02 08 01 10:29:39 02 08 01 10:44:41
    04 08 01 09:57:57 04 08 01 01:08:33
    06 08 01 06:13:45 06 08 01 10:12:37


    Mine start date and end date is always the same . What I want is exact
    time
    difference between start_dt and end_dt.
    ( Like from first row I want 00:15:02 ) (Time difference between end_dt
    and start_dt in hours and minutes format)
    I have tried various conversion functions but of no avail. Can anybody
    help?

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    select to_char(end_dt-start_dt ,'hh:mi:ss') from ocean.proc_bch_job_sts where prg_nm='UOQ0150' and
    rownum < 4;

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Sorry Boss notworking

    Invalid number format. This is the error when I try to run ur query.

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Must be my access double background... you can do it without the to_char

    select end_dt-start_dt Dif from ocean.proc_bch_job_sts where prg_nm='UOQ0150' and
    rownum < 4;
    It gives you the part of a day, so if you do this

    select (end_dt-start_dt)*24*60*60 Secs from ocean.proc_bch_job_sts where prg_nm='UOQ0150' and
    rownum < 4;

    It gives you the seconds passed....

    Regards

Posting Permissions

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