This discussion is archived
10 Replies Latest reply: Dec 30, 2012 1:37 AM by Stew Ashton RSS

Difference between date datatype

Chanchal Wankhade Journeyer
Currently Being Moderated
hi all,

We have oracle 10G R2 on windows.

we have table called set_1 as
SQL> desc set_1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------
 SL_NO                                              NUMBER
 STARTTIME                                          DATE
 ENDTIME                                            DATE
in the table we have data like
SQL> ed
Wrote file afiedt.buf

  1  select sl_no,to_char(starttime,'dd-mm-yyyy hh24:mi:ss')starttime,
  2  to_char(endtime,'dd-mm-yyyy hh24:mi:ss')endtime
  3* from set_1 where rownum<=5
SQL> /

     SL_NO STARTTIME           ENDTIME
---------- ------------------- -------------------
         4 11-12-2012 13:15:00 11-12-2012 13:15:36
        12 11-12-2012 15:15:00 11-12-2012 15:15:01
        20 11-12-2012 17:15:00 11-12-2012 17:15:12
        29 11-12-2012 19:30:00 11-12-2012 19:30:01
        41 11-12-2012 22:30:00 11-12-2012 22:30:01
I want to retrive the data which will have fourth column difference between starttime and endtime in minuts and seconds only.
for that i wrote query but showing error...
select TO_CHAR(STARTTIME,'DD-MM-YYYY HH24:MI:SS') STARTTIME,TO_CHAR(ENDTIME,'DD-MM-YYYY HH24:MI:SS') ENDTTIME,
TO_CHAR(STARTTIME,'MM:SS')-TO_CHAR(ENDTIME,'MM:SS') DIFFERENCE FROM SET_1;


Error

TO_CHAR(STARTTIME,'MM:SS')-TO_CHAR(ENDTIME,'MM:SS') DIFFERENCE FROM SET_1
*
ERROR at line 2:
ORA-01722: invalid number
Can anybody help me to achive this...
  • 1. Re: Difference between date datatype
    sb92075 Guru
    Currently Being Moderated
    TO_CHAR() results in character string so what what results when you subtract "A" from "B" (which are strings)?
  • 2. Re: Difference between date datatype
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    hi sb,

    ok, but then how can i get desire result. by using which function.
  • 3. Re: Difference between date datatype
    sb92075 Guru
    Currently Being Moderated
    Chanchal Wankhade wrote:
    hi sb,

    ok, but then how can i get desire result. by using which function.
    I give up.
    what is result that you desire?
  • 4. Re: Difference between date datatype
    Stew Ashton Expert
    Currently Being Moderated
    Chanchal Wankhade wrote:
    difference between starttime and endtime in minuts and seconds only.
    That can mean one of two things:
    1) Get the difference between the two dates and show only minutes and seconds, even if the difference is greater than a day.
    2) Take just the minutes and seconds from each date and show the difference of that.

    I am assuming 1) because 2) doesn't make much sense.

    To get the difference between two dates, just subtract one from the other to get a fractional day, adding an arbitrary date that you will not show.

    Use TO_CHAR only to format the final result.
    WITH DATA AS (
      SELECT TO_DATE('11-12-2012 13:15:00','dd-mm-yyyy hh24:mi:ss')STARTTIME,
      TO_DATE('11-12-2012 13:15:36','dd-mm-yyyy hh24:mi:ss') ENDTIME
      FROM DUAL
    )
    SELECT DATA.*, 
    to_char(to_date((date '2000-01-01'-starttime)+endtime), 'HH24:MI:SS') difference
    from data;
    
    STARTTIME            ENDTIME              DIFFERENCE
    -------------------- -------------------- ----------
    11-DEC-12 13:15:00   11-DEC-12 13:15:36   00:00:36
  • 5. Re: Difference between date datatype
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    hi stew,,

    It showing nothing in difference in Oracle 10G R2.
    SQL> WITH DATA AS (
      2    SELECT TO_DATE('11-12-2012 13:15:00','dd-mm-yyyy hh24:mi:ss')STARTTIME,
      3    TO_DATE('11-12-2012 13:15:36','dd-mm-yyyy hh24:mi:ss') ENDTIME
      4    FROM DUAL
      5  )
      6  SELECT DATA.*,
      7  to_char(to_date((date '2000-01-01'-starttime)+endtime), 'HH24:MI:SS') difference
      8  from data;
    
    STARTTIME ENDTIME   DIFFEREN
    --------- --------- --------
    11-DEC-12 11-DEC-12 00:00:00
    it's showing result in your query but if i past same query in my command prompt it does not show actual difference and shows 00 00 00.....
  • 6. Re: Difference between date datatype
    Stew Ashton Expert
    Currently Being Moderated
    Could you copy, paste and run this version and post the result, please?
    WITH DATA AS (
      SELECT TO_DATE('11-12-2012 13:15:00','dd-mm-yyyy hh24:mi:ss')STARTTIME,
      TO_DATE('11-12-2012 13:15:36','dd-mm-yyyy hh24:mi:ss') ENDTIME
      FROM DUAL
    )
    SELECT TO_CHAR(STARTTIME,'dd-mm-yyyy hh24:mi:ss') STARTTIME,
    TO_CHAR(ENDTIME,'dd-mm-yyyy hh24:mi:ss') ENDTIME, 
    NUMTODSINTERVAL(ENDTIME-STARTTIME, 'DAY') INTERVAL_DIFF, 
    TO_CHAR(TO_DATE((DATE '2000-01-01'-STARTTIME)+ENDTIME), 'HH24:MI:SS') date_diff
    from data;
  • 7. Re: Difference between date datatype
    Etbin Guru
    Currently Being Moderated
    I want to retrive the data which will have fourth column difference between starttime and endtime in minuts and seconds only.
    I might got it wrong, but it seems you want where endtime - starttime < 1 / 24

    Regards

    Etbin
  • 8. Re: Difference between date datatype
    rp0428 Guru
    Currently Being Moderated
    >
    I want to retrive the data which will have fourth column difference between starttime and endtime in minuts and seconds only.
    >
    Always start with something simple.

    You can use the NUMTODSINTERVAL function to convert a number (e.g. the difference in seconds between two date values) to days, hours, minutes and seconds.

    See the SQL Language doc
    http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions117.htm
    WITH DATA AS (
      SELECT TO_DATE('11-12-2012 13:15:00','dd-mm-yyyy hh24:mi:ss')STARTTIME,
      TO_DATE('11-12-2012 13:15:36','dd-mm-yyyy hh24:mi:ss') ENDTIME
      FROM DUAL
      UNION ALL
      SELECT TO_DATE('11-12-2012 13:15:00','dd-mm-yyyy hh24:mi:ss')STARTTIME,
      TO_DATE('29-12-2012 09:29:31','dd-mm-yyyy hh24:mi:ss') ENDTIME
      FROM DUAL
    )
    SELECT DATA.*, numtodsinterval((endtime - starttime) * 86400, 'SECOND') myDIFF
    from data;
    
    STARTTIME,ENDTIME,MYDIFF
    12/11/2012 1:15:00 PM,12/11/2012 1:15:36 PM,+00 00:00:36.000000
    12/11/2012 1:15:00 PM,12/29/2012 9:29:31 AM,+17 20:14:31.000000
    If that is not what you want then you will need to write a function to compute the exact form that you want.

    Here is a Java sample that converts a time in milliseconds to HH:MI:SS.nnnn format. You would use a similar PL/SQL function.
    /**
     *  elapsed time in hours/minutes/seconds/milliseconds
     * @return String
     */
    public static String getHoursMinutesSecondsFromMilliTime(long milliTime) {
        int milliSecondsPerSecond = 1000;
        int milliSecondsPerMinute = 60 * milliSecondsPerSecond;
        int milliSecondsPerHour   = 60 * milliSecondsPerMinute;
        long hours;
        long minutes;
        long seconds;
        long milliSeconds;
        long modRemainder;
        String result;
     
        hours = milliTime / milliSecondsPerHour;
        modRemainder = milliTime - (hours * milliSecondsPerHour);
        minutes = modRemainder / milliSecondsPerMinute;
        modRemainder = modRemainder - (minutes * milliSecondsPerMinute);
        seconds = modRemainder / milliSecondsPerSecond;
        milliSeconds = modRemainder - (seconds * milliSecondsPerSecond);
     
        result = hours + ":" + minutes + ":" + seconds + ":" + milliSeconds;
        return result;
    }
  • 9. Re: Difference between date datatype
    ascheffer Expert
    Currently Being Moderated
    You have a to_date which you don't need, use
    WITH DATA AS (
      SELECT TO_DATE('11-12-2012 13:15:00','dd-mm-yyyy hh24:mi:ss')STARTTIME,
      TO_DATE('11-12-2012 13:15:36','dd-mm-yyyy hh24:mi:ss') ENDTIME
      FROM DUAL
    )
    SELECT TO_CHAR(STARTTIME,'dd-mm-yyyy hh24:mi:ss') STARTTIME,
    TO_CHAR(ENDTIME,'dd-mm-yyyy hh24:mi:ss') ENDTIME, 
    NUMTODSINTERVAL(ENDTIME-STARTTIME, 'DAY') INTERVAL_DIFF, 
    TO_CHAR( ( DATE '2000-01-01'-STARTTIME ) + ENDTIME, 'HH24:MI:SS') date_diff
    from data;
  • 10. Re: Difference between date datatype
    Stew Ashton Expert
    Currently Being Moderated
    ascheffer wrote:
    You have a to_date which you don't need, use
    ...
    TO_CHAR( ( DATE '2000-01-01'-STARTTIME ) + ENDTIME, 'HH24:MI:SS') date_diff
    ...
    I could have sworn I tried exactly that and got a number instead of a date, so I added TO_DATE as a workaround. Right now I cannot reproduce that behavior, so I must have done something else wrong. Thanks for the correction.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points