Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Unanswered: Urgent-(DB2 Function)

    I am writing a script in which I need to put a date function which will automatically generate the first business day of the current month and also the first business day of the last month(i.e. Monday). Its a script which will run report every month and I would need that function which would automatically pull up the business days as mentioned above.
    Please advise.

    SCRIPT:

    -- first get list of servers added in the last month
    with new_srvrs(physerial, invnum, phyhost, allocxdate) as (
    select physerial, invnum, phyhost, allocxdate from inv_old_daily
    where allocxdate='2009-08-03'
    and INVNUM>(select max(INVNUM) from inv_old_daily where allocxdate='2009-07-01'))
    ,
    Last edited by thrm_t; 10-04-09 at 23:27.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    thrm t, check out this link for
    Scalar functions - IBM DB2 9.7 for Linux, UNIX, and Windows
    (It if for DB2 LUW V9.7 since you didn't mention the DB2 version you are using or the operating system).

    Specifically, look at NEXT_DAY and LAST_DAY (by adding 1 day you get the first of the next month). A combination of these with a little Date Arithmetic should get you the values you need. DAYOFWEEK may also be useful.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Current month:

    select
    case
    when dayofweek_iso (substr(current_date, 1,8)||'01') = 6
    then substr(current_date, 1,8)||'03'
    when dayofweek_iso (substr(current_date, 1,8)||'01') = 7
    then substr(current_date, 1,8)||'02'
    else substr(current_date, 1,8)||'01'
    end
    from sysibm.sysdummy1

    Previous month:

    select
    case
    when dayofweek_iso (substr(current_date - 1 month, 1,8)||'01') = 6
    then substr(current_date - 1 month, 1,8)||'03'
    when dayofweek_iso (substr(current_date - 1 month, 1,8)||'01') = 7
    then substr(current_date - 1 month, 1,8)||'02'
    else substr(current_date - 1 month, 1,8)||'01'
    end
    from sysibm.sysdummy1
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    What about Mondays and Fridays that are your business's holidays? You might want your own calendar table.

    Dave Nance

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are two examples using date calculation on DB2.

    "first biz day of month (1)" will work on DB2 9.7 for LUW and older.
    "first biz day of month (2)" will work on DB2 9.7 for LUW only(LAST_DAY was used).
    (I didn't check on other platform.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CURRENT_DATE AS CURRENT_DATE
         , CURRENT_DATE
           - (DAY(CURRENT_DATE)
              - SIGN( MOD( DAY(CURRENT_DATE) + 11 - DAYOFWEEK_ISO(CURRENT_DATE), 7 ) - 5 )
              - 2) DAYS
             AS "first biz day of month (1)"
         , CURRENT_DATE
           - (DAY(CURRENT_DATE)
              + SIGN( MOD( DAYOFWEEK_ISO(LAST_DAY(CURRENT_DATE - 1 MONTH)) + 2, 7 ) - 1 )
              - 2) DAYS
             AS "first biz day of month (2)"
      FROM sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    CURRENT_DATE first biz day of month (1) first biz day of month (2)
    ------------ -------------------------- --------------------------
    2009-10-06   2009-10-01                 2009-10-01                
    
      1 record(s) selected.

  6. #6
    Join Date
    Jun 2002
    Posts
    15

    Urgent-(DB2 Function)

    in LUW 9.7...

    Code:
    select 
     current date as current_date
    ,next_day(last_day(current date - 1 month),'MONDAY') as first_monday_of_current_month
    ,next_day(last_day(current date - 2 month),'MONDAY') as first_monday_of_last_month
    from sysibm.sysdummy1
    ; 
    
    CURRENT_DATE FIRST_MONDAY_OF_CURRENT_MONTH FIRST_MONDAY_OF_LAST_MONTH
    ------------ ----------------------------- --------------------------
    10/05/2009   10/05/2009                    09/07/2009                
    
      1 record(s) selected.

Posting Permissions

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