Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Difference in weeks between dates

    Hi,
    DB2 version 9.1 - im trying to get the number of week boundaries crossed between two dates, where Monday is defined as a new week.

    For example, November 8th 2009 (Sunday) and November 9th 2009 (Monday) would result in a value of 1 as a single week boundary has been crosseed, even though the difference in days is not a full week. This should also work when the dates are in different months or years e.g. December 31st 2009 (Thursday) and January 2nd 2010 (Saturday) would result in zero as they are the same week even though different years.

    This is done in SQL Server using datediff(week, date1, date2) - is there something similar in DB2? I know there is function TIMESTAMPDIFF but this is only an approximation which is not acceptable.

    I'm thinking WEEK_ISO(CURRENT DATE) may be of some use but i'm not sure where to start.

    Thanks in advance

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    let's say d1 and d2 are the columns in table dts representing start date and end date for considerations .. then your query will be
    Code:
    select (julian_day(D2)-julian_day(D1)+(dayofweek_iso(d1)-1))/7 from dts
    for testing, i used
    Code:
    with dts(d1,d2,comment) as
    (
    values('2009-11-09','2009-11-15','- no bounday crossed, 7 days'),
           ('2009-11-09','2009-11-16' , '-- boundary corssed, > 7 days'),
           ('2009-11-13','2009-11-15', '-- no bounday corssed, < 7 days'),
           ('2009-11-15','2009-11-16' , '--- boundary crossed < 7 days'),
           ('2009-12-31','2010-01-04','-- diff years, < 7 days, boundary crossed'),
           ('2009-12-31','2010-01-01','-- diff years, < 7 days, no bound crossed')
           
    )
    select d2,dayofweek_iso(d2),d1,dayofweek_iso(d1),comment,(julian_day(D2)-julian_day(D1)) as days_ct,
    (julian_day(D2)-julian_day(D1)+(dayofweek_iso(d1)-1))/7 as weekboundary_count from dts

    (dayofweek_iso(d1)-1)) is the adjustment factor to start counting days from Monday of the week of d1

    hope this helps

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    I'd go for a diff. in days, and dived by 7 to get weeks. That would be most accurate:
    Code:
    db2 "select (int(days(Current_date) / 7)  - int(days(current_date - 33 year) / 7)) as diffweek From sysibm.sysdummy1"

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Just, curious ... Why is this more accurate ???

    I tried using 16/11 and 15/11 , which according to the op, must be 1 .. but this query seems to return 0


    Quote Originally Posted by dr_te_z View Post
    I'd go for a diff. in days, and dived by 7 to get weeks. That would be most accurate:
    Code:
    db2 "select (int(days(Current_date) / 7)  - int(days(current_date - 33 year) / 7)) as diffweek From sysibm.sysdummy1"
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2009
    Posts
    37

    Thumbs up

    sathyaram_s

    That seems to work perfectly, ill have to deconstruct it to see how its working but it is!! I never thought of using julian_days....

    You are again correct that "dr_te_z"'s post does not work for 15 Nov and 16 Nov, which should return 1 as they are Sunday and Monday respectively - his query does indeed return 0.

    I am probably being a bit cheeky here but would it be possible to convert your query to give the number of month boundaries crosses? The start of the month is obviously the first of the month and it work across years (as before) and must account for leap years.

    thanks a mill, I really do appreciate your help

  6. #6
    Join Date
    Nov 2009
    Posts
    37
    Actually is that query "reversible" i.e. if there are 3 weeks between dateA and dateB then there should be -3 weeks between dateB and dateA.
    The following examples return 3 and 1 where for dateA, dateB but they return -2 and 0 for dateB, dateA i.e. they are one out for the reverse

    Code:
    with dts(d1, d2, comment) as
    (
        values 
           ('2009-12-31','2010-01-18','-- diff years, 3 bound crossed'),
           ('2010-01-18','2009-12-31','-- diff years, -2 bound crossed, this should be -3'),
           ('2009-11-15','2009-11-16' , '--- boundary crossed < 7 days'),
           ('2009-11-16', '2009-11-15', '--- boundary crossed < 7 days, should be -1')
    )
    select d2, dayofweek_iso(d2) ISO_day_of_week_d2, d1, dayofweek_iso(d1) ISO_day_of_week_d1, comment, (julian_day(D2) - julian_day(D1)) as days_ct,
    (julian_day(D2) - julian_day(D1) + (dayofweek_iso(d1) - 1)) / 7 as weekboundary_count from dts
    Can I just add a CASE statement to offset the result by 1 if dateB > dateA?

    thanks

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by FLANDERS View Post

    Can I just add a CASE statement to offset the result by 1 if dateB > dateA?

    thanks
    You can ...
    But a logical way is to have an adjustment factor ..
    If d2>d1, then you apply adjustment to get to count days from Monday before d1 upto d2
    If d1>d2, then you apply the adjustment factor to count from Sunday after d1 and backwards upto d2

    Code:
    with dts(d1, d2, comment) as
    (
        values 
           ('2009-12-31','2010-01-18','-- diff years, 3 bound crossed'),
           ('2010-01-18','2009-12-31','-- diff years, -2 bound crossed, this should be -3'),
           ('2009-11-15','2009-11-16' , '--- boundary crossed < 7 days'),
           ('2009-11-16', '2009-11-15', '--- boundary crossed < 7 days, should be -1'),
           ('2009-11-30','2009-11-29','--no boundary crossed, must be 0')
    )
    select d2, dayofweek_iso(d2) ISO_day_of_week_d2, d1, dayofweek_iso(d1) ISO_day_of_week_d1, 
    comment, 
    (julian_day(D2) - julian_day(D1)) as days_ct,
    (julian_day(D2) - julian_day(D1) + ( 
    case 
    when d2>d1 then (dayofweek_iso(d1) - 1)
    else (-1)*(7-dayofweek_iso(d1) )
    end
    )) / 7  as weekboundary_count from dts
    Last edited by sathyaram_s; 11-13-09 at 08:38. Reason: pasted the wrong code. corrected now.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    ------------------------------ Commands Entered ------------------------------
    with dts(d1, d2, comment) as
    (values 
           ('2009-11-09', '2009-11-15', '-- no bounday crossed, 7 days'),
           ('2009-11-09', '2009-11-16', '-- boundary corssed, > 7 days'),
           ('2009-11-13', '2009-11-15', '-- no bounday corssed, < 7 days'),
           ('2009-11-15', '2009-11-16', '-- boundary crossed < 7 days'),
           ('2009-12-31', '2010-01-04', '-- diff years, < 7 days, boundary crossed'),
           ('2009-12-31', '2010-01-01', '-- diff years, < 7 days, no bound crossed'),
           ('2010-01-18', '2009-12-31', '-- diff years, -2 bound crossed, this should be -3'),
           ('2009-11-16', '2009-11-15', '-- boundary crossed < 7 days, should be -1')
    )
    SELECT d1, d2
         , JULIAN_DAY(d2) / 7 - JULIAN_DAY(d1) / 7 AS weekboundary_count
         , comment
      FROM dts;
    ------------------------------------------------------------------------------
    
    D1         D2         WEEKBOUNDARY_COUNT COMMENT                                           
    ---------- ---------- ------------------ --------------------------------------------------
    2009-11-09 2009-11-15                  0 -- no bounday crossed, 7 days                     
    2009-11-09 2009-11-16                  1 -- boundary corssed, > 7 days                     
    2009-11-13 2009-11-15                  0 -- no bounday corssed, < 7 days                   
    2009-11-15 2009-11-16                  1 -- boundary crossed < 7 days                      
    2009-12-31 2010-01-04                  1 -- diff years, < 7 days, boundary crossed         
    2009-12-31 2010-01-01                  0 -- diff years, < 7 days, no bound crossed         
    2010-01-18 2009-12-31                 -3 -- diff years, -2 bound crossed, this should be -3
    2009-11-16 2009-11-15                 -1 -- boundary crossed < 7 days, should be -1        
    
      8 record(s) selected.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... to give the number of month boundaries crosses?
    YEAR(d2) * 12 + MONTH(d2) - ( YEAR(d1) * 12 + MONTH(d1) )

  10. #10
    Join Date
    Nov 2009
    Posts
    37
    Double beers!


    Yeah that offset within the case worked a charm as well.

    Regarding the months boundaries, I assume it would be along the same lines but I would need to know how many days are in the month in which d1 resides (rather than just using a constant like you did with 7 days in a week)?

    I was thinking along the lines of this
    Code:
    values day(DATE('2009-12-15') - day('2009-12-15') days)
    but that gives me the number of days from the previous month, on November 30th.

    Adding one month to the date as follows doesnt quite work as it giees December 30th as the date and hence 30 days but it should be 31
    Code:
    values day(DATE('2009-12-15') - day('2009-12-15') days + 1 month)

  11. #11
    Join Date
    Nov 2009
    Posts
    37

    Cool

    @@Tonkuma

    Beautiful, both solutions seem to work perfectly, thanks a million, thank you sathyaram_s also



  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Red face

    Easy one ... hmmm .. Didn't think Julian Calendar starts on a Monday

    Quote Originally Posted by tonkuma View Post
    SELECT d1, d2
    , JULIAN_DAY(d2) / 7 - JULIAN_DAY(d1) / 7 AS weekboundary_count
    , comment
    FROM dts;

    [/CODE]
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  13. #13
    Join Date
    Nov 2009
    Posts
    37
    Quote Originally Posted by sathyaram_s View Post
    Easy one ... hmmm .. Didn't think Julian Calendar starts on a Monday
    Didnt realise that either! It appears it does, from Julian day - Wikipedia, the free encyclopedia:

    Code:
    Name             Current Epoch 
    Julian Date (JD) 	12:00 January 1, 4713 BC, Monday

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question Simple SQL is not good ?

    I cant understand the problem, at all....

    Why you don't want to use simple SQL, like this :
    (days(date2) - days(date1)) / 7
    Code:
    select (days(current date) - days(current date - 1 year))/7
    from sysibm.sysdummy1;
    
    select (days(current date) - days(current date - 1 month))/7
    from sysibm.sysdummy1;
    
    select (days(current date) - days(current date - 11 day))/7
    from sysibm.sysdummy1;
    
    select (days(current date) - days(current date - 42 day))/7
    from sysibm.sysdummy1;
    Lenny

  15. #15
    Join Date
    Nov 2009
    Posts
    37
    Quote Originally Posted by Lenny77 View Post
    I cant understand the problem, at all....


    Code:
    (days(date2) - days(date1)) / 7
    That doesnt work. For example
    Code:
    values (days('2009-11-16') - days('2009-11-15')) / 7
    returns 0 when I need it to return 1. Since the 15th is a Sunday and the 16th is a Monday, a week boundary has been crossed, hence the result needs to be 1 (even though the dates are only 1 day apart)

Posting Permissions

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