Is there a Date based function for this?

db2user24 · #1 (**permalink**) 08-11-10, 21:27

Hi,

I was wondering if DB2 has an inbuilt function or if anyone has ever created a function that will take a date and return the 'week of the month' that it falls under? for example, Aug 11, 2010 would return 2 since it falls in the second week of August... thanks for the help!!

DB2Plus · #2 (**permalink**) 08-11-10, 21:42

No, it is not !

db2girl · #3 (**permalink**) 08-11-10, 22:01

There is 'week of the year' and 'day of the week':

db2 "values week (current date)"

1
-----------
33

1 record(s) selected.

db2 "values dayofweek (current date)"

1
-----------
4

1 record(s) selected.

tonkuma · #4 (**permalink**) 08-11-10, 22:28

It may be rather simple expression to calculate 'week of the month'.

Some examples:

Code:

------------------------------ Commands Entered ------------------------------
SELECT date_
     , WEEK(date_) - WEEK( date_ - ( DAY(date_) - 1 ) DAYs ) + 1 AS week_of_month_a
     , WEEK(date_) - WEEK( LEFT( CHAR(date_ , ISO) , 8 ) || '01' ) + 1 AS week_of_month_b
  FROM (VALUES DATE('2010-08-11') , DATE('2010-09-12') ) AS d(date_);
------------------------------------------------------------------------------

DATE_      WEEK_OF_MONTH_A WEEK_OF_MONTH_B
---------- --------------- ---------------
2010-08-11               2               2
2010-09-12               3               3

  2 record(s) selected.

db2user24 · #5 (**permalink**) 08-12-10, 11:52

thanks, if i run this command at the command line, i get this error --

db2 "SELECT date_ , WEEK(date_) - WEEK( date_ - ( DAY(date_) - 1 ) DAYs ) + 1 AS week_of_month_a , WEEK(date_) - WEEK( LEFT( CHAR(date_ , ISO) , 8 ) || '01' ) + 1 AS week_of_month_b FROM (VALUES DATE('2010-08-11') , DATE('2010-09-12')) AS d(date_)"
SQL0440N No authorized routine named "WEEK" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

I can get the date back though --

db2 "SELECT date_ FROM (VALUES DATE('2010-08-11') , DATE('2010-09-12')) AS d(date_)"
DATE_
----------
08/11/2010
09/12/2010

2 record(s) selected.

What am I doing wrong? Thanks!!

tonkuma · #6 (**permalink**) 08-12-10, 12:54

1) What are your DB2 version/release and platform OS?

2) You can check your code little by little like:

Code:

------------------------------ Commands Entered ------------------------------
SELECT date_ , WEEK(date_)
/* - WEEK( date_ - ( DAY(date_) - 1 ) DAYs ) + 1 AS week_of_month_a , WEEK(date_) - WEEK( LEFT( CHAR(date_ , ISO) , 8 ) || '01' ) + 1 AS week_of_month_b
*/
 FROM (VALUES DATE('2010-08-11') , DATE('2010-09-12')) AS d(date_);
------------------------------------------------------------------------------

DATE_      2          
---------- -----------
2010-08-11          33
2010-09-12          38

  2 record(s) selected.

Code:

------------------------------ Commands Entered ------------------------------
SELECT date_ , WEEK(date_)
 - WEEK( date_ - ( DAY(date_) - 1 ) DAYs ) + 1 AS week_of_month_a 
/* , WEEK(date_) - WEEK( LEFT( CHAR(date_ , ISO) , 8 ) || '01' ) + 1 AS week_of_month_b
*/
 FROM (VALUES DATE('2010-08-11') , DATE('2010-09-12')) AS d(date_);
------------------------------------------------------------------------------

DATE_      WEEK_OF_MONTH_A
---------- ---------------
2010-08-11               2
2010-09-12               3

  2 record(s) selected.

db2user24 · #7 (**permalink**) 08-12-10, 13:05

Thanks! We are using DB2 8.2 / Linux platform.. I tried it again.. week_of_month_a works just fine but week_of_month_b doesn't... not sure why.. but week_of_month_a is good enough..thanks!!

tonkuma · #8 (**permalink**) 08-12-10, 13:38

You are using DB2 8.2 / Linux platform.

So, my guess was that LEFT function returned VARCHAR(4000) and SUBSTR( CHAR(date_ , ISO) , 1 , 8 ) may resolve the error.

Note: DB2 8.2 has only sysfun.LEFT which returns VARCHAR(4000), while DB2 9.5 or later has sysibm.LEFT which returns VARCHAR(second parameter).

db2user24 · #9 (**permalink**) 08-12-10, 14:57

Thanks, basically I'm trying to figure out if I can determine if a certain day is a holiday.. for example Memorial day is the 5th Monday of May.. when I run the above command, I get the week for 5/31/2010 ( for example) as 6 which is correct. But it's not giving me what I really want.. which is 5. since it's the 5th Monday in May. Any idea how I can get this result?

Here's another example -- Columbus Day is the 2nd Monday of October and this year it's on Oct 11th, 2010.. I want a function that will return 2.. if I try what you gave me.. it returns 3 which is technically correct as it's the week it falls under but again I want to know which Monday of the month it corresponds to. Thanks!!

tonkuma · #10 (**permalink**) 08-12-10, 15:18

How about this?

Code:

------------------------------ Commands Entered ------------------------------
SELECT CHAR(date_ , USA) AS date
     , ( DAY(date_) + 6 ) / 7 AS weekday_of_month
  FROM (VALUES DATE('5/31/2010') , DATE('10/11/2010') ) AS d(date_);
------------------------------------------------------------------------------

DATE       WEEKDAY_OF_MONTH
---------- ----------------
05/31/2010                5
10/11/2010                2

  2 record(s) selected.