Difference in weeks between dates

FLANDERS · #1 (**permalink**) 11-12-09, 18:18

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

sathyaram_s · #2 (**permalink**) 11-13-09, 01:20

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

dr_te_z · #3 (**permalink**) 11-13-09, 01:55

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"

sathyaram_s · #4 (**permalink**) 11-13-09, 05:42

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

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"

FLANDERS · #5 (**permalink**) 11-13-09, 06:14

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

FLANDERS · #6 (**permalink**) 11-13-09, 06:50

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

sathyaram_s · #7 (**permalink**) 11-13-09, 07:33

Quote:

Originally Posted by FLANDERS

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

tonkuma · #8 (**permalink**) 11-13-09, 09:31

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.

tonkuma · #9 (**permalink**) 11-13-09, 09:36

Quote:

... to give the number of month boundaries crosses?

YEAR(d2) * 12 + MONTH(d2) - ( YEAR(d1) * 12 + MONTH(d1) )

FLANDERS · #10 (**permalink**) 11-13-09, 10:03

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)

FLANDERS · #11 (**permalink**) 11-13-09, 10:16

@@Tonkuma

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

sathyaram_s · #12 (**permalink**) 11-13-09, 10:36

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

Quote:

Originally Posted by tonkuma

SELECT d1, d2
, JULIAN_DAY(d2) / 7 - JULIAN_DAY(d1) / 7 AS weekboundary_count
, comment
FROM dts;

[/CODE]

FLANDERS · #13 (**permalink**) 11-13-09, 10:47

Quote:

Originally Posted by sathyaram_s

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

Lenny77 · #14 (**permalink**) 11-13-09, 10:53

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

Why you don't want to use simple SQL, like this :

Quote:

(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

FLANDERS · #15 (**permalink**) 11-13-09, 11:08

Quote:

Originally Posted by Lenny77

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech