Urgent-(DB2 Function)

thrm_t · #1 (**permalink**) 10-04-09, 22:18

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'))
,

Stealth_DBA · #2 (**permalink**) 10-04-09, 22:38

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.

Marcus_A · #3 (**permalink**) 10-05-09, 00:29

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

dav1mo · #4 (**permalink**) 10-05-09, 08:13

What about Mondays and Fridays that are your business's holidays? You might want your own calendar table.

Dave Nance

tonkuma · #5 (**permalink**) 10-05-09, 13:18

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.

yvanroy · #6 (**permalink**) 10-05-09, 17:35

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech