1 2 Previous Next 15 Replies Latest reply: Jul 15, 2011 5:14 AM by bluefrog RSS

    Datdiff issue

    819711
      In sql server the following qouery return 3

      select RTRIM(CAST(DATEDIFF(WEEK,'27-JUL-2011','13-AUG-2011')+1 AS CHAR(2)))

      and oracle it return 4

      select RTRIM(TO_CHAR(CEIL((to_date('13-AUG-2011') - to_date('27-JUL-2011')) / 7) + 1)) from dual

      . as a quick fix we used the round function. but for the following test case it is failing again.


      select RTRIM(CAST(DATEDIFF(WEEK,'02-JAN-2012','01-JAN-2011')+1 AS CHAR(10))) --- sql server retrun -52
      in oracle the following query retrun -51
      select RTRIM(TO_CHAR(ROUND((to_date('02-JAN-2011') - to_date('01-JAN-2012')) / 7) + 1)) from dual

      any help will be appreacited.
        • 1. Re: Datdiff issue
          LPS
          select (to_date('02-JAN-2011') - to_date('01-JAN-2012')) / 7 + 1 from dual

          Itself is enough giving

          -51
          • 2. Re: Datdiff issue
            819711
            Yeah but it still their is difference with sql server :(
            • 3. Re: Datdiff issue
              bluefrog
              In SQL Server you're comparing in weeks, whereas in Oracle you're attempting to do the same thing, but by obtaining the number of weeks by dividing the number of days by 7. The week number can however be different to the week number;
              for example;
              SQL>  select to_char(to_date('311211','ddmmyy'), 'ww') as week_no
                2  from dual;
              
              WE
              --
              53
              is 53, whereas if you divide by 7 you'll get
              SQL> select round(to_char(to_date('311211','ddmmyy'), 'ddd') /7)
                2  as week_no from dual;
              
                 WEEK_NO
              ----------
                      52
              So for your answer to be the same as SQL Server you have to compare week to week as follows ;
              SQL> ed
              Wrote file afiedt.buf
              
                1  select    to_char(to_date('130811','ddmmyy'),'yyyyww')
                2          - to_char(to_date('270711','ddmmyy'),'yyyyww') as Week_Diff
                3* from dual
              SQL> /
              
               WEEK_DIFF
              ----------
                       3
              • 4. Re: Datdiff issue
                819711
                for certain case it's not working.

                like

                select to_char(to_date('020111','ddmmyy'),'yyyyww')
                - to_char(to_date('020112','ddmmyy'),'yyyyww') as Week_Diff
                from dual

                it retrun -100. in sqlserver it retrun -51
                select RTRIM(CAST(DATEDIFF(WEEK,'02-JAN-2012','02-JAN-2011')+1 AS CHAR(10)))
                • 5. Re: Datdiff issue
                  bluefrog
                  Apologies, one has to account for the year;
                  SQL> with d as (select
                    2              to_date('020112','ddmmyy') as  d1
                    3             ,to_date('020111','ddmmyy') as  d2
                    4             from dual)
                    5  ,t as (select
                    6              to_char(d1,'ww') as d1_week
                    7             ,to_char(d2,'ww') as d2_week
                    8             ,to_char(d1,'yyyy') as d1_year
                    9             ,to_char(d2,'yyyy') as d2_year
                   10         from d)
                   11  --
                   12  select ( (d2_year - d1_year) * 52 ) + (d2_week - d1_week) + 1 as Week_Diff
                   13  from t
                   14  /
                  
                   WEEK_DIFF
                  ----------
                         -51
                  • 6. Re: Datdiff issue
                    819711
                    it working for the different year but not working in the first test case

                    WITH d AS
                    (SELECT to_date('27-JUL-2011', 'dd-mon-yyyy') AS d1,
                    to_date('13-AUG-2011', 'dd-mon-yyyy') AS d2
                    FROM dual),
                    t AS
                    (SELECT to_char(d1, 'ww') AS d1_week,
                    to_char(d2, 'ww') AS d2_week,
                    to_char(d1, 'yyyy') AS d1_year,
                    to_char(d2, 'yyyy') AS d2_year
                    FROM d)
                    SELECT ((d2_year - d1_year) * 52) + (d2_week - d1_week) + 1 AS Week_Diff
                    FROM t;

                    return 4 but in sql server it retrun 3.
                    • 7. Re: Datdiff issue
                      bluefrog
                      SQL> with d as (select
                        2              to_date('020112','ddmmyy') as  d1
                        3             ,to_date('020111','ddmmyy') as  d2
                        4             from dual)
                        5  ,t as (select
                        6              to_char(d1,'ww') as d1_week
                        7             ,to_char(d2,'ww') as d2_week
                        8             ,to_char(d1,'yyyy') as d1_year
                        9             ,to_char(d2,'yyyy') as d2_year
                       10             ,(to_char(d2,'yyyy') - to_char(d1,'yyyy')) as year_diff
                       11         from d)
                       12  --
                       13  select ( year_diff * 52 ) + (d2_week - d1_week) + (case year_diff when 0 then 0 else 1 end)
                       14         as Week_Diff
                       15  from t
                       16  /
                      
                       WEEK_DIFF
                      ----------
                             -51
                      
                      SQL> 
                      SQL> with d as (select
                        2              to_date('270711','ddmmyy') as  d1
                        3             ,to_date('130811','ddmmyy') as  d2
                        4             from dual)
                        5  ,t as (select
                        6              to_char(d1,'ww') as d1_week
                        7             ,to_char(d2,'ww') as d2_week
                        8             ,to_char(d1,'yyyy') as d1_year
                        9             ,to_char(d2,'yyyy') as d2_year
                       10             ,(to_char(d2,'yyyy') - to_char(d1,'yyyy')) as year_diff
                       11         from d)
                       12  --
                       13  select ( year_diff * 52 ) + (d2_week - d1_week) + (case year_diff when 0 then 0 else 1 end)
                       14         as Week_Diff
                       15  from t
                       16  /
                      
                       WEEK_DIFF
                      ----------
                               3
                      • 8. Re: Datdiff issue
                        819711
                        one test case fails.

                        with d as (select
                        to_date('010111','ddmmyy') as d1
                        ,to_date('020112','ddmmyy') as d2
                        from dual)
                        ,t as (select
                        to_char(d1,'ww') as d1_week
                        ,to_char(d2,'ww') as d2_week
                        ,to_char(d1,'yyyy') as d1_year
                        ,to_char(d2,'yyyy') as d2_year
                        ,(to_char(d2,'yyyy') - to_char(d1,'yyyy')) as year_diff
                        from d)
                        select ( year_diff * 52 ) + (d2_week - d1_week) + (case year_diff when 0 then 0 else 1 end)
                        as Week_Diff
                        from t

                        this return 53. in sql server 54.
                        • 9. Re: Datdiff issue
                          189821
                          Why add 1 to the result? Why not
                          SQL>select CEIL((TO_DATE('13-AUG-2011') - TO_DATE('27-JUL-2011')) / 7) as ddiff from dual;
                          
                               DDIFF
                          ----------
                                   3
                          
                          SQL>select CEIL((TO_DATE('2-JAN-2011') - TO_DATE('1-JAN-2012')) / 7) as ddiff from dual;
                          
                               DDIFF
                          ----------
                                 -52 
                          Urs
                          • 10. Re: Datdiff issue
                            bluefrog
                            refer to the following documentation;

                            http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch9sql.htm#CIHGFJEI

                            >
                            If January 1 falls on a Friday, Saturday, or Sunday, then the week including January 1 is the last week of the previous year, because most of the days in the week belong to the previous year.

                            If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, then the week is the first week of the new year, because most of the days in the week belong to the new year.

                            >

                            Also, you'll have to experiment with the "iw" format mask.
                            • 11. Re: Datdiff issue
                              819711
                              select RTRIM(CAST(DATEDIFF(WEEK,'27-JUL-2011','13-AUG-2011') AS CHAR(2))) it rertun 2
                              the corrosponding query in oracle select CEIL((TO_DATE('13-AUG-2011') - TO_DATE('27-JUL-2011')) / 7) as ddiff from dual retrun 3
                              • 12. Re: Datdiff issue
                                189821
                                In your original post you claimed
                                n sql server the following qouery return 3
                                select RTRIM(CAST(DATEDIFF(WEEK,'27-JUL-2011','13-AUG-2011')+1 AS CHAR(2))) >
                                I am confused now.

                                And there are so many ways to spell rertun... ;-)

                                Urs
                                • 13. Re: Datdiff issue
                                  bluefrog
                                  Generally, using the format mask "yyyyww" to determine the difference will always give you the same figure as SQL Server, apart from when comparing a day that falls in either the 1st or last week of the year with another day that falls in the 1st week of the following year, because of the way that Oracle divides up a week.

                                  http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch9sql.htm#autoId19

                                  So based on the above documentation, if the day falls on a Mon to Thu then the 1st week of the year is selected, whereas if Fri to Sun, then the last week of the previous year is selected. If you want to bypass this rule, you have to account for it in your SQL code. If you count the number of weeks difference between the 1st Jan 11 and 2nd Jan 12, you'll notice that the number of weeks difference is in fact 53 if you apply the rule. the "WEEK" format mask in SQL Server might be applying a different rule.
                                  • 14. Re: Datdiff issue
                                    819711
                                    finally it's done.all you have to do to count the number of sunday in between two dates.

                                    CREATE OR REPLACE FUNCTION datediff(p_type_in IN VARCHAR2,
                                    p_date_in1 IN DATE,
                                    p_date_in2 IN DATE) RETURN NUMBER AS

                                    l_flag NUMBER(1) := 1;
                                    l_date DATE;
                                    l_counter NUMBER := 0;

                                    l_sql_id NUMBER;

                                    l_date_in1 DATE := p_date_in1;
                                    l_date_in2 DATE := p_date_in2;

                                    BEGIN

                                    l_sql_id := 10;

                                    IF (UPPER(p_type_in) = 'WEEK' OR UPPER(p_type_in) = 'WK') THEN
                                    IF (l_date_in1 > l_date_in2) THEN
                                    WHILE (l_flag = 1) LOOP
                                    l_date := next_day(l_date_in2, 'SUN');
                                    IF (l_date <= l_date_in1) THEN
                                    l_counter := l_counter + 1;
                                    l_date_in2 := l_date;
                                    ELSE
                                    l_flag := 0;
                                    END IF;
                                    END LOOP;
                                    END IF;

                                    l_sql_id := 20;
                                    IF (l_date_in1 < l_date_in2) THEN
                                    WHILE (l_flag = 1) LOOP
                                    l_date := next_day(l_date_in1, 'SUN');
                                    IF (l_date <= l_date_in2) THEN
                                    l_counter := l_counter + 1;
                                    l_date_in1 := l_date;
                                    ELSE
                                    l_flag := 0;
                                    END IF;

                                    END LOOP;
                                    END IF;

                                    IF (p_date_in1 > l_date_in2) THEN
                                    l_counter := -l_counter;
                                    END IF;
                                    END IF;

                                    RETURN l_counter;

                                    END datediff;

                                    thanks everyone for the valuable suggestions.
                                    1 2 Previous Next