Add two business days to a date

[gwilburn]

I've searched and found many similar situations... but I have not found the exact answer. Given a date, I would like to calculate the date two (or 3, or 4) business days in the future.

I have a DATE_CONV table created for DB2 WebQuery... among other fields, it has DC_WEEKEND='Y' or 'N" and DC_HOLIDAY='Y' or 'N'.

I just don't know enough about SQL to use this table to get the next business day.

[jtaylor___]

What about holidays?

[jtaylor___]

Scratch that. I missed your holiday column.

[jtaylor___]

What do your tables look like?

[gwilburn]

One row per date through 12/31/2030... can join to it using any date format.

Each row has anything and everything you would want to know about a date:
Date (date format)
JDE Julian Date (CYYDDD decimal)
Date (MMDDYYYY packed decimal)
Date (MMDDYYYY zoned decimal)
Date (MMDDYYYY character)
Date (YYYYMMDD packed decimal)
Date (YYYYMMDD zoned decimal)
Date (YYYYMMDD character)
Date (MMDDYY packed decimal)
Date (MMDDYY zoned decimal)
Date (MMDDYY character)
Date (YYMMDD packed decimal)
Date (YYMMDD zoned decimal)
Date (YYMMDD character)
Century (2 characters)
Year (2 characters)
Month (2 characters)
Day (2 characters)
Year (4 digits)
Day of week (1-7)
Day of week (1-7)
Day of year (1-366)
Week of year (1-52)
Week of year (1-53)
Quarter of year (1-4)
Century (2 digits)
Year (2 digits)
Month (2 digits)
Day (2 digits)
Century, Year, Month CCYYMM (6 digits)
Day Name (Monday,etc.)
Quarter name (2008Q1)
Weekend Flag (Y or N)
Holiday (Y or N)
Day Before Holiday (Y or N)
Day AfterHoliday (Y or N)
Full Moon (Y or N)
Season (Spring, Summer, Autumn, Winter)
Fiscal year (4 digits)
Fiscal quarter (1-4)
Month name (January, etc)
Month abbreviation (Jan, Feb, etc)
Date in Julian format
CYYMMDD packed C = 0 for 1900 & C = 1 for
Date in Excel format
Week starting date (the prior Saturday)
Week ending date (the next Friday)
Same day last year
Current Day (Y/N)
Current Week (Y/N)
Current Month (Y/N)
Current Quarter (Y/N)
Current Year (Y/N)
Current Year to Date (Y/N)
Current Day Last Year (Y/N)
Current Week Last Year (Y/N)
Current Month Last Year (Y/N)
Current Quarter Last Year (Y/N)
Current Year Last Year (Y/N)
Current Year To Date Last Year (Y/N)
Previous Day (Y/N)
Previous Week (Y/N)
Previous Month (Y/N)
Previous Quarter (Y/N)
Previous Year (Y/N)
Previous fiscal year (Y/N)
Current fiscal year (Y/N)
Previous fiscal year to date (Y/N)
Current fiscal year to date (Y/N)
Nth Day of the Week of the month

[jtaylor___]

See if something like this works for you. The CTE gets the first 2 days after today that are neither weekends nor holidays. The second SELECT gets the final date from the CTE.

with TwoDays as (
select DATE
  from TABLE
  where DATE > current_date
   and WEEKEND = 'N'
   and HOLIDAY = 'N'
order by DATE
fetch first 2 rows only   
)
select *
  from TwoDays
order by DATE desc
fetch first 1 row only

[gwilburn]

jtaylor thank you!!

I've been beating around this bush all afternoon. I find all kinds of examples for other databases (even DB2 on the mainframe) that do not seem to work. But this DOES WORK.

It's odd... but I'm creating an SQL view of our data... An order has to be shipped within 2 business days of receipt.

I'm placing this logic in an SQLRPGLE procedure to use in my SQL view. I know it seems convoluted, but I have other logic involved that's easier to handle in RPG (the time and type of order). The type of the order will make the number of days variable.

I was hoping I could simply tell it to give me the "nth" row of a select statement. Evidently that's not possible on the IBM i??

[jtaylor___]

...
I was hoping I could simply tell it to give me the "nth" row of a select statement. Evidently that's not possible on the IBM i??

Excellent question. I checked the Db2 ref, and presto!

select DATE
  from TABLE
  where DATE > current_date
      and WEEKEND = 'N'
      and HOLIDAY = 'N'
order by DATE
offset 2 rows
fetch first 1 row only

[B.Hauser]

Even Shorter (and may be more elegant)

Select YourDate
  from Table
  Where    YourDate   > current_date
       and WeekEnd = 'N'
       and Holiday = 'N'
  Order By YourDate
[B]Limit 2, 1[/B];

Birgitta

[kitvb1]

Very nice JT and Biggie.
Point to note though is that the offset of 2 (first parameter of the LIMIT cluase) would return the date 3 days in advance. Thus, "Limit 0, 1;" would return the next working day.
To return a date in the past (e.g. for reminder letters), you would need to change not only the compare symbol, but also the sequence to descending.

 Select YourDate
  from Table
  Where    YourDate   [B][COLOR=#FF0000]<[/COLOR][/B] current_date
       and WeekEnd = 'N'
       and Holiday = 'N'
  Order By YourDate [B][COLOR=#FF0000]desc[/COLOR][/B]
[B]Limit 2, 1[/B];

[gwilburn]

All,

Really, really nice information here! The "limit" and "offset" are new to me.

I don't understand why it's so hard to find examples in a google search. I actually found some SQL for DB2 examples that didn't seem to work - they were for z-OS.

Thanks again. You all are great!

Greg

[gwilburn]

Apparently the SQL pre-compiler does not like the "OFFSET" or "LIMIT" functions. I'm getting a SQL0104 error "Position 20 Token 1 not valid. Valid Tokens: FETCH"

I'm running RDi v9.6 on a v7r1 box.

[B.Hauser]

Check whether you are on the current PTF-Level in Release 7.1.
LIMIT and OFFSET where introduced for Release 7.2 but made available for Release 7.1, too (via PTF SF99701 Level 38)

Birgitta

[gwilburn]

SF99701 is permanently applied...

This works in ACS Run SQL Scripts. Been using it all morning. It just won't compile when embedded in SQLRPGLE.

[EmmanuelW1]

SF99701 isn't a PTF, it's a PTF group (DB2 for i), so it can't be applied permanently, although all of the PTFs in the group could be, I suppose.

Use WRKPTFGRP PTFGRP(SF99701) to see the level installed on your system, and whether it's equal to or higher than 38.

Cheers,

Emmanuel

[gwilburn]

I've opened a PMR with IBM. SF99701 is listed below. The RDi v9.6 syntax checker also reports that same error (inline in when coding).

PTF Group Level Status Text
SF99710 17192 Installed CUMULATIVE PTF PACKAGE C7192710
SF99709 214 Installed GROUP HIPER
SF99709 215 Installed GROUP HIPER
SF99709 216 Installed GROUP HIPER
SF99708 76 Installed GROUP SECURITY
SF99708 77 Installed GROUP SECURITY
SF99708 78 Installed GROUP SECURITY
SF99707 11 Installed TECHNOLOGY REFRESH
SF99705 40 Installed HARDWARE AND RELATED PTFS
SF99701 43 Installed DB2 FOR IBM I
SF99657 6 Installed DB2 WEB QUERY FOR I V2.1.1
SF99647 17 Installed DB2 WEB QUERY FOR I V2.1.0
SF99627 11 Installed 7.1 ELECTRONIC SERVICES GROUP PTF
SF99572 30 Installed JAVA
SF99368 52 Installed IBM HTTP SERVER FOR I

[Viking]

OFFSET and LIMIT are super useful, thanks!

I was just playing with them and see that they work well for me when I run them in ACS Run SQL Scripts, and also in Surveyor/400 (Linoma). They do not seem to work on the green screen with STRSQL. I have not tried within an RPG program yet or in my RDi v9.5. I'm on i7.2.