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?

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • BigKat
    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
    8,200 pointsBadges:
    report
  • TomN
    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.
    50 pointsBadges:
    report
  • BigKat
    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
    8,200 pointsBadges:
    report
  • TomN
    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?
    50 pointsBadges:
    report
  • GAC
    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.
    300 pointsBadges:
    report
  • Sloopy
    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
    2,195 pointsBadges:
    report
  • graybeard52
    >> and have not been able to make anything work to do this. << What happens when you try BigKat's suggestion? That is using RPG .
    3,115 pointsBadges:
    report
  • Sloopy
    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
    2,195 pointsBadges:
    report
  • BigKat
    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
    8,200 pointsBadges:
    report
  • TomN
    Thanks for all your replies... I am sad to tell you that I have not had any exposure to RPGLE first hand. We have some old apps that I have needed to do small updates to over the years but all our programs are "older style" RPG II code that was brought forward by our software company back in '98 with very little change to the code and we were supplied the source code to do some customizations to if we needed to, although it was frowned upon. Most of our "custom" reports have been created by us using the Sequel product from HelpSystems, which has performed beautifully for us these last 9 years or so. We have been in the process of upgrading to our software company’s newer versions and I am seeing more and more ILE in their libraries. The thing I am trying to work with is one of our older apps and have been looking for a way to do this in the "old" code structure (that I am used to). I have just built something using SQL and a short little RPG program that I think will do the trick. I pass the output to a temp file and then read it using the little RPG program and place the date in the needed LDA positions for the final program to grab. One line of script: EXECUTE SQL('select "&&Adate" name(ldavalA) from sequelu/sqlexec') OUTFILE(QTEMP/QQ) REPLACE(*YES) With a variable defined like this: SQL(MMDDYY(current date+(6-dayofweek(current date))days)) Then read the file and put the data where needed: H FRILDA H*... Reads date in temp file and puts in LDA positions desired FQQ IP F 10 DISK IQQ NS 01 I 1 60FRIDA I UDS I 151 1560FRIDA Now I think I have heard that in ILE you can embed SQL statements, if this is true, could that SQL be placed right in the RPG and do it in one step? This is working for me now but am interested in looking further into “newer style” code. All this is just to reduce repetitious operator input and reduce errors!
    50 pointsBadges:
    report
  • Gilly400
    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.
    23,730 pointsBadges:
    report
  • takeoption3please
    report
  • takeoption3please
    Sorry, the previous all came out as a link, but I'm new to this forum. - jdl
    20 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • BigKat
    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 :(
    8,200 pointsBadges:
    report
  • DoneThat
    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?
    830 pointsBadges:
    report
  • BigKat
    Hi DoneThat, that was Jlanus syntax, (see my prev message - I just added the word "day" after the case "end") -- and I just ran the "char ( current date ... end day, ISO)" in a select from sysibm.sysdummy1like you did Which was how I realized it needed the "day" I really wasn't trying to steal the credit
    8,200 pointsBadges:
    report
  • DoneThat
    OK BK...I must confess I'm not great at reading/responding to forums. Maybe Jlanus will read my inquiry. I would start a new thread but the history of the question is all tied up in this thread. Would be a heck of a convoluted post. Til next time
    830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following