## Date calculation question

50 pts.
Tags:
AS/400 date format
Date calculation
Date format
I have a date manipulation question on the AS/400. I need to be able to take whatever date the system date is and calculate the next Friday date and output that to the LDA in the format MMDDYY. It needs to have the leading zero for the single digit months AND it is just a 2-digit year. I swear I had something in the pre-Y2k (System/36) days that did something similar to this but have had no luck finding it! ...or remembering! Anyone got any ideas?

Thanks. We'll let you know when a new response is added.

Check it out –> http://www.itjungle.com/fhg/fhg113005-story01.html

For determining what is a Friday, you use a known reference point, such as 1/1/1900 and are able to mathematically return the day-of-week from that with some formulas. (detailed in the article)

As far as jumping ahead a week, try

<pre> myvar = %dec(%date(%timestamp()) + %days(7));
</pre>
Test if the date is a Friday. If not…

<pre> myvar = myvar + %days(1);
</pre>
… and test again. Or, test <pre>myvar </pre>in the first place to see what it is using the day-of-week formula from the article and then add the appropriate number of days from that.

Does that make sense?

Another approach, cannot test it as I don’t have DB2 available now.
It uses a common table expression to return <pre>dateOfNextFriday </pre> as the current date plus a number found by calculating all 7 next dates and returning the one that’s a Friday (dayOfWeek = 6).
That result is input to the <pre>char()</pre> function to make it ISO format.
Then that result is the source for various <pre>substr()</pre> functions that build the desired format.
<pre> values
with t(dateOfNextFriday) as (
char(
current date +
case
when dayOfWeek(current date + 1 day) = 6 then 1
when dayOfWeek(current date + 2 day) = 6 then 2
when dayOfWeek(current date + 3 day) = 6 then 3
when dayOfWeek(current date + 4 day) = 6 then 4
when dayOfWeek(current date + 5 day) = 6 then 5
when dayOfWeek(current date + 6 day) = 6 then 6
when dayOfWeek(current date + 7 day) = 6 then 7
end day,
ISO)
) select
— yyyy-mm-dd
— 1234 67 90
substr(dateOfNextFriday, 6, 2)
|| substr(dateOfNextFriday, 9, 2)
|| substr(dateOfNextFriday, 3, 2)
as DateOfNextFridayMMDDYYYY;
</pre>
The <pre>values </pre> will let you run the query in a command line tool and see if it works.

## Discuss This Question: 18 Replies

Thanks. We'll let you know when a new response is added.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
• assuming by "the next Friday" you mean that Sunday - Thursday goes to Friday this week, while Friday and Saturday go to Friday next week:
```diff = 4 - %rem(%diff(this_date:%date('0001-01-01'):*days):7);
if diff <= 0;
diff += 7;
endif;
next_friday = this_date + %days(diff);
```
if by "the next Friday" you mean that all days go to Friday of next week:
```next_friday = this_date + %days(11 - %rem(%diff(this_date:%date('0001-01-01'):*days):7));
```
Kevin C. Ketzler - Affiliated
report
• Well, actually, if you were running this on a Friday, then it would need to put "today's" Friday date, so then the next progression would be Saturday and that would need to produce the following Friday's date. Thanks for the replies so far, they are greatly appreciated! I have not had a chance to test out things yet but hope to over the next few days.
report
• OK, for that you use basically use the first code only like this:
```diff = 4 - %rem(%diff(this_date:%date('0001-01-01'):*days):7);
if diff < 0;
diff += 7;
endif;
next_friday = this_date + %days(diff);
```
Kevin C. Ketzler - Affiliated
report
• Hi again... thanks for the responses. I am still having a problem with this, however, and I have to make this work using an RPG program and/or a CL, possibly SQL, and have not been able to make anything work to do this. We don't have a C complier, so, any suggestions to accomplish this in these other ways?
report
• Tom, You can use SQL function DayOfWeek(Date_Value). It returns an integer for each day of the week, being 6 a Friday. Embedding it into the RPG program will let you keep the logic you already have done so far. Gerardo.
report
• See this article: http://systeminetwork.com/node/26456 ...and look up CEEDATE on the IBM site. You can use calls to this utility to find your friday, by first finding out what today's day of week is, and then to format that date in exactly the way you want it. Regards, Sloopy
report
• >> and have not been able to make anything work to do this. << What happens when you try BigKat's suggestion? That is using RPG .
report
• OK. Try this code:
```D SysDate         S               D  Inz(*SYS)
D WorkISOdate     S             10A
D LillianDays     S             10I 0
D DayOfWeek       S             10I 0
D NextFriday      S              6A

* Get the Lillian date from the System date :

C                   Eval    WorkISOdate = %Char( SysDate : *ISO )
C                   Call    CEEDAYS ( WorkISOdate : 'YYYY-MM-DD' : LillianDays )

* Find out what weekday today is (Sunday = 1) :

C                   Call    CEEDYWK ( LillianDays : DayOfWeek )

* Add the right number of days to get to next Friday :

C                   Select
C                   When    DayOfWeek = 1
C                   Eval    LillianDays = LillianDays + 5
C                   When    DayOfWeek = 2
C                   Eval    LillianDays = LillianDays + 4
C                   When    DayOfWeek = 3
C                   Eval    LillianDays = LillianDays + 3
C                   When    DayOfWeek = 4
C                   Eval    LillianDays = LillianDays + 2
C                   When    DayOfWeek = 5
C                   Eval    LillianDays = LillianDays + 1
C                   When    DayOfWeek = 6
C                   Eval    LillianDays = LillianDays + 7
C                   When    DayOfWeek = 7
C                   Eval    LillianDays = LillianDays + 6
C                   EndSl

* Now convert the date :

C                   Call    CEEDATE ( LillianDays : 'MMDDYY' : NextFriday )
```
Regards, Sloopy
report
• Unless there is a typo I am not seeing, my free format RPG(LE) code should have worked unless you are on an older O/S level??? Kevin
report
report
• Hi TomN, You can embed SQL in "normal" RPG/400 as well as ILE. You'll need to make the source type SQLRPG for RPG/400 or SQLRPGLE for ILE RPG, then SEU will error check the embedded SQL for you and you can compile using option 14 from PDM. Regards, Martin Gilbert.
report
• report
• Sorry, the previous all came out as a link, but I'm new to this forum. - jdl
report
• Now I think I have heard that in ILE you can embed SQL statements... You can indeed, as you also can with "Old" RPG. However... You need to have the SQL compiler option to do that. I see that you're using an "EXECUTE" command to run SQL in a CL program. That suggests that you don't have clean access to QM, so you probably don't have the SQL compiler option. (They come in one product.) It doesn't matter much if your programs are "Old" RPG or newer ILE. What matters most is the version of the operating system. If it's been kept up and you can still compile your old programs, then you can also compile ILE versions. All of the compilers are in a single package (until the very latest releases in 2010.) Tom
report
• I wish there was an option to update the answer, without it looking like I am claiming the WHOLE answer. I added one word - day - after the case statement, and well, now it shows I wrote it :(
report
• Hello BK. Maybe you can help me with something? I wrote the following to make your SQL example work...
```WITH 	dummy AS (
SELECT
CHAR(
CURRENT DATE +
CASE
WHEN DAYOFWEEK(CURRENT DATE + 1 day) = 6 THEN 1
WHEN DAYOFWEEK(CURRENT DATE + 2 day) = 6 THEN 2
WHEN DAYOFWEEK(CURRENT DATE + 3 day) = 6 THEN 3
WHEN DAYOFWEEK(CURRENT DATE + 4 day) = 6 THEN 4
WHEN DAYOFWEEK(CURRENT DATE + 5 day) = 6 THEN 5
WHEN DAYOFWEEK(CURRENT DATE + 6 day) = 6 THEN 6
WHEN DAYOFWEEK(CURRENT DATE + 7 day) = 6 THEN 7
END 	day,
ISO)
AS dateOfNextFriday
FROM	sysibm.sysdummy1
)
SELECT
SUBSTR(dateOfNextFriday, 6, 2)
|| SUBSTR(dateOfNextFriday, 9, 2)
|| SUBSTR(dateOfNextFriday, 1, 4)
AS DateOfNextFridayMMDDYYYY
FROM	dummy
WITH	NC;
```
But YOU wrote as a CTE with a different syntax i.e. with t(dateOfNextFriday) etc. I cannot make that work. Can you mod my example above using your syntax?