10 Replies Latest reply: Dec 30, 2012 3:37 AM by Stew Ashton RSS

    Difference between date datatype

    Chanchal Wankhade
      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
          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
            hi sb,

            ok, but then how can i get desire result. by using which function.
            • 3. Re: Difference between date datatype
              sb92075
              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
                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
                  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
                    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
                      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
                        >
                        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
                          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
                            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.