12 Replies Latest reply: Aug 17, 2012 7:41 AM by EdStevens RSS

    Difference between two dates

    Costa
      I have two tables master and market
      of which market contain three variables like sale start_date, stop_date and duration (difference between start and stop date). I want to retrieve those data
      where difference between start and stop date is not matching with duration.
      start_date stop_date duration
      13-JAN-2012 14-JAN-2012 1 day
      18-JAN-2012 18-MAR-2012 3 month
      22-APR-2012 22-APR-2012 1 month
      09-JUL-2011 09-JUL-2012 365 days
      18-JAN-2012 18-MAR-2012 59 days
      select a.start_date,a.stop_date,a.duration
      from master b, market a
      where a.number=b.number
      and start_date is not null and stop_date is not null
      and duration is not null
      and stop_date-start_date=duration
        • 1. Re: Difference between two dates
          Khayyam
          Costa wrote:
          I have two tables master and market
          of which market contain three variables like sale start_date, stop_date and duration (difference between start and stop date). I want to retrieve those data
          where difference between start and stop date is not matching with duration.
          start_date stop_date duration
          13-JAN-2012 14-JAN-2012 1 day
          18-JAN-2012 18-MAR-2012 3 month
          22-APR-2012 22-APR-2012 1 month
          09-JUL-2011 09-JUL-2012 365 days
          18-JAN-2012 18-MAR-2012 59 days
          select a.start_date,a.stop_date,a.duration
          from master b, market a
          where a.number=b.number
          and start_date is not null and stop_date is not null
          and duration is not null
          and stop_date-start_date=duration
          in which format you store data in Duration column ? number ? date - date gives number.. But it seems like you gor Varchar (1 day)... Please clarify that moment..
          • 2. Re: Difference between two dates
            Costa
            Duration is in varchar2 format.
            How can I match the difference with duration.
            • 3. Re: Difference between two dates
              Purvesh K
              With few assumptions:

              Month = 30 days
              Year = 365 Days
              1/Many Day = 1 Days

              I hope this matches your requirement. If not do point the incorrect record output.
              with data(st_date, ed_date, dur) as
              (
              select to_date('13-JAN-2012', 'DD-MON-YYYY'), to_date('14-JAN-2012', 'DD-MON-YYYY'), '1 days' from dual union all
              select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-MAR-2012', 'DD-MON-YYYY'), '3 month' from dual union all
              select to_date('22-APR-2012', 'DD-MON-YYYY'), to_date('22-APR-2012', 'DD-MON-YYYY'), '1 month' from dual union all
              select to_date('09-JUL-2011', 'DD-MON-YYYY'), to_date('09-JUL-2012', 'DD-MON-YYYY'), '365 days' from dual union all
              select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-MAR-2012', 'DD-MON-YYYY'), '59 days' from dual
              ),
              conv_data as
              (
                select st_date, ed_date, dur,
                       case
                         when instr(dur, 'month') >= 1
                           then substr(dur, 1, instr(dur, ' ')) * 30
                         when instr(dur, 'year') >= 1
                           then substr(dur, 1, instr(dur, ' ')) * 365
                         else
                           substr(dur, 1, instr(dur, ' ')) * 1
                       end dur_days
                  from data
              )
              select st_date, ed_date, dur
                from (
                       select ed_date, st_date, dur, 
                              count (
                                      case when (dur_days - (ed_date - st_date)) = 0
                                             then null
                                           else 
                                             1
                                      end
                                    ) cnt
                         from conv_data
                        group by ed_date, st_date, dur
                     ) a
               where a.cnt != 0;
              • 4. Re: Difference between two dates
                BluShadow
                Purvesh K wrote:
                With few assumptions:

                Month = 30 days
                Year = 365 Days
                1/Many Day = 1 Days

                I hope this matches your requirement. If not do point the incorrect record output.
                with data(st_date, ed_date, dur) as
                (
                select to_date('13-JAN-2012', 'DD-MON-YYYY'), to_date('14-JAN-2012', 'DD-MON-YYYY'), '1 days' from dual union all
                select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-MAR-2012', 'DD-MON-YYYY'), '3 month' from dual union all
                select to_date('22-APR-2012', 'DD-MON-YYYY'), to_date('22-APR-2012', 'DD-MON-YYYY'), '1 month' from dual union all
                select to_date('09-JUL-2011', 'DD-MON-YYYY'), to_date('09-JUL-2012', 'DD-MON-YYYY'), '365 days' from dual union all
                select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-MAR-2012', 'DD-MON-YYYY'), '59 days' from dual
                ),
                conv_data as
                (
                select st_date, ed_date, dur,
                case
                when instr(dur, 'month') >= 1
                then substr(dur, 1, instr(dur, ' ')) * 30
                when instr(dur, 'year') >= 1
                then substr(dur, 1, instr(dur, ' ')) * 365
                else
                substr(dur, 1, instr(dur, ' ')) * 1
                end dur_days
                from data
                )
                select st_date, ed_date, dur
                from (
                select ed_date, st_date, dur, 
                count (
                case when (dur_days - (ed_date - st_date)) = 0
                then null
                else 
                1
                end
                ) cnt
                from conv_data
                group by ed_date, st_date, dur
                ) a
                where a.cnt != 0;
                There isn't always 30 days in a month or 365 days in a year, so why not use proper oracle date functionality to do it and let oracle handle them properly...
                SQL> ed
                Wrote file afiedt.buf
                
                  1  with t as (
                  2             select to_date('13-JAN-2012', 'DD-MON-YYYY') as s, to_date('14-JAN-2012', 'DD-MON-YYYY') as e, '1 days' as dur from dual union all
                  3             select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-MAR-2012', 'DD-MON-YYYY'), '3 month' from dual union all
                  4             select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-APR-2012', 'DD-MON-YYYY'), '3 month' from dual union all
                  5             select to_date('22-APR-2012', 'DD-MON-YYYY'), to_date('22-APR-2012', 'DD-MON-YYYY'), '1 month' from dual union all
                  6             select to_date('09-JUL-2011', 'DD-MON-YYYY'), to_date('09-JUL-2012', 'DD-MON-YYYY'), '365 days' from dual union all
                  7             select to_date('01-JUL-2011', 'DD-MON-YYYY'), to_date('09-JUL-2012', 'DD-MON-YYYY'), '1 year' from dual union all
                  8             select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-MAR-2012', 'DD-MON-YYYY'), '59 days' from dual
                  9            )
                 10  --
                 11  -- end of test data
                 12  --
                 13  select s, e, dur
                 14        ,case when instr(dur,'day') > 0 then
                 15                s+to_number(regexp_substr(dur, '\d+'))
                 16              when instr(dur,'mon') > 0 then
                 17                add_months(s,to_number(regexp_substr(dur, '\d+')))
                 18              when instr(dur,'year') > 0 then
                 19                add_months(s,12*to_number(regexp_substr(dur, '\d+')))
                 20         else null
                 21         end as actual_end
                 22        ,case when instr(dur,'day') > 0 then
                 23                case when s+to_number(regexp_substr(dur, '\d+')) != e then 'Wrong' else 'Right' end
                 24              when instr(dur,'mon') > 0 then
                 25                case when add_months(s,to_number(regexp_substr(dur, '\d+'))) != e then 'Wrong' else 'Right' end
                 26              when instr(dur,'year') > 0 then
                 27                case when add_months(s,12*to_number(regexp_substr(dur, '\d+'))) != e then 'Wrong' else 'Right' end
                 28         else 'Unknown Duration'
                 29         end as chk
                 30* from t
                SQL> /
                
                S           E           DUR      ACTUAL_END  CHK
                ----------- ----------- -------- ----------- ----------------
                13-JAN-2012 14-JAN-2012 1 days   14-JAN-2012 Right
                18-JAN-2012 18-MAR-2012 3 month  18-APR-2012 Wrong
                18-JAN-2012 18-APR-2012 3 month  18-APR-2012 Right
                22-APR-2012 22-APR-2012 1 month  22-MAY-2012 Wrong
                09-JUL-2011 09-JUL-2012 365 days 08-JUL-2012 Wrong
                01-JUL-2011 09-JUL-2012 1 year   01-JUL-2012 Wrong
                18-JAN-2012 18-MAR-2012 59 days  17-MAR-2012 Wrong
                
                7 rows selected.
                • 5. Re: Difference between two dates
                  Purvesh K
                  There isn't always 30 days in a month or 365 days in a year, so why not use proper oracle date functionality to do it and let oracle handle them properly...
                  Yes, correct. I did not think this thoroughly earlier, I was however working on similar lines to generalize my previous query. Thanks for correcting me.
                  • 6. Re: Difference between two dates
                    Costa
                    Thanks to both of you.
                    While running the SQL, I am getting error like
                    ORA-00923: FROM keyword not found where expected
                    Can you help me plz
                    • 7. Re: Difference between two dates
                      Purvesh K
                      I am able to execute the Query.

                      Did you take the entire query before executing? If you skip the With Clause, you might face such an issue.

                      Can you post the query you used for executing?

                      Also can you post which version of Oracle 9i are you using?

                      Result from
                      select * from v$version;
                      Edited by: Purvesh K on Aug 17, 2012 3:19 PM
                      • 8. Re: Difference between two dates
                        Costa
                        Dear Purvesh,

                        I am using
                        Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi.
                        • 9. Re: Difference between two dates
                          APC
                          Costa wrote:
                          While running the SQL, I am getting error like
                          ORA-00923: FROM keyword not found where expected
                          Probably you introduced a typo when you transferred/translated the posted code to your database.

                          We cannot diagnose syntax errors in your code unless you post it, but frankly you would learn more if you found your bloomer yourself.

                          Cheers, APC
                          • 10. Re: Difference between two dates
                            BluShadow
                            Costa wrote:
                            Dear Purvesh,

                            I am using
                            Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi.
                            Odd. I wondered why Purvesh has asked you which version of 9i, but then saw you'd put a tag for 9i on the thread (it's usually better to detail you database version within your post, as that's what people are generally reading)... but what's odd is that you now say your database version is 10.2.0.3 which isn't 9i at all.

                            In 9i the regular expression functions in my query wouldn't have worked, though they will work in 10.2.0.3.
                            If you're on 9i, then you could use something like the translate function or other string functions to extract the numeric part of the duration...

                            e.g.
                            SQL> ed
                            Wrote file afiedt.buf
                            
                              1  with t as (
                              2             select to_date('13-JAN-2012', 'DD-MON-YYYY') as s, to_date('14-JAN-2012', 'DD-MON-YYYY') as e, '1 days' as dur from dual union all
                              3             select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-MAR-2012', 'DD-MON-YYYY'), '3 month' from dual union all
                              4             select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-APR-2012', 'DD-MON-YYYY'), '3 month' from dual union all
                              5             select to_date('22-APR-2012', 'DD-MON-YYYY'), to_date('22-APR-2012', 'DD-MON-YYYY'), '1 month' from dual union all
                              6             select to_date('09-JUL-2011', 'DD-MON-YYYY'), to_date('09-JUL-2012', 'DD-MON-YYYY'), '365 days' from dual union all
                              7             select to_date('01-JUL-2011', 'DD-MON-YYYY'), to_date('09-JUL-2012', 'DD-MON-YYYY'), '1 year' from dual union all
                              8             select to_date('18-JAN-2012', 'DD-MON-YYYY'), to_date('18-MAR-2012', 'DD-MON-YYYY'), '59 days' from dual
                              9            )
                             10  --
                             11  -- end of test data
                             12  --
                             13  select s, e, dur
                             14        ,case when instr(dur,'day') > 0 then
                             15                case when s+to_number(substr(dur,1,instr(dur,' ')-1)) != e then 'Wrong' else 'Right' end
                             16              when instr(dur,'mon') > 0 then
                             17                case when add_months(s,to_number(substr(dur,1,instr(dur,' ')-1))) != e then 'Wrong' else 'Right' end
                             18              when instr(dur,'year') > 0 then
                             19                case when add_months(s,12*to_number(substr(dur,1,instr(dur,' ')-1))) != e then 'Wrong' else 'Right' end
                             20         else 'Unknown Duration'
                             21         end as chk
                             22* from t
                            SQL> /
                            
                            S                    E                    DUR      CHK
                            -------------------- -------------------- -------- ----------------
                            13-JAN-2012 00:00:00 14-JAN-2012 00:00:00 1 days   Right
                            18-JAN-2012 00:00:00 18-MAR-2012 00:00:00 3 month  Wrong
                            18-JAN-2012 00:00:00 18-APR-2012 00:00:00 3 month  Right
                            22-APR-2012 00:00:00 22-APR-2012 00:00:00 1 month  Wrong
                            09-JUL-2011 00:00:00 09-JUL-2012 00:00:00 365 days Wrong
                            01-JUL-2011 00:00:00 09-JUL-2012 00:00:00 1 year   Wrong
                            18-JAN-2012 00:00:00 18-MAR-2012 00:00:00 59 days  Wrong
                            
                            7 rows selected.
                            • 11. Re: Difference between two dates
                              BluShadow
                              Costa wrote:
                              Dear Purvesh,

                              I am using
                              Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi.
                              Perhaps the confusion over you tagging with 9i and saying your database is 10.2.0.3 is to do with your version of SQL*Plus?

                              If you are using SQL*Plus from version 9i, then you should upgrade your client software to match the database version, otherwise it won't understand all the syntax of the database version you are using.
                              • 12. Re: Difference between two dates
                                EdStevens
                                Costa wrote:
                                I have two tables master and market
                                of which market contain three variables like sale start_date, stop_date and duration (difference between start and stop date). I want to retrieve those data
                                where difference between start and stop date is not matching with duration.
                                Which is exactly why one of the fundamental principals of database design is that you do not store information that can be calculated from other informtion. You should eliminate either stop_date or duration. If you keep duration, it should be a numeric, counting days.

                                Don't forget that DATE also includes time, so you might need to use the trunc() function when dealing with your dates.
                                start_date stop_date duration
                                13-JAN-2012 14-JAN-2012 1 day
                                18-JAN-2012 18-MAR-2012 3 month
                                22-APR-2012 22-APR-2012 1 month
                                09-JUL-2011 09-JUL-2012 365 days
                                18-JAN-2012 18-MAR-2012 59 days
                                select a.start_date,a.stop_date,a.duration
                                from master b, market a
                                where a.number=b.number
                                and start_date is not null and stop_date is not null
                                and duration is not null
                                and stop_date-start_date=duration