Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Is there a Date based function for this?

    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!!

  2. #2
    Join Date
    Jul 2009
    Posts
    150
    No, it is not !

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.
    Last edited by db2girl; 08-11-10 at 23:13.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    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!!

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    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!!

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).
    Last edited by tonkuma; 08-12-10 at 18:13.

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    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!!

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  11. #11
    Join Date
    Nov 2007
    Posts
    265
    Wow, you are good!! Will try it out.. thx..

  12. #12
    Join Date
    Nov 2007
    Posts
    265
    Excellent, it works like a charm.. thank you SO much!! Can you explain how this works?

  13. #13
    Join Date
    Nov 2007
    Posts
    265
    Another question -- how can you form the date 5/31/2010 based on this information ?

    Month = 5
    DayOfWeek = 2
    Week of Month = 5

Posting Permissions

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