## Determine future end of month based on any given date

50 pts.
Tags:
RPG
I need your help in determining a future end of month based on user entries. For example if a user enters a date of 2005.11.30, the routine would return 2005.12.31. Or if the user enters a date of 2005.12.31, the routine would return 2006.01.31. It must always be one month in advance and also should consider leap years. Thank you very much. IBlearning

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

What language are you coding this in?

======================================================

If your OS is current, you might be interested in the SQL LAST_DAY( date-expression ) function. Provide some date expression like CURRENT_DATE + 1 MONTH and LAST_DAY() will return the date of the last day of the resulting month. Requires at least i5/OS V5R4.

Tom

## Discuss This Question: 9 Replies

Thanks. We'll let you know when a new response is added.
• Whilst some languages (or environments such as .NET) have such functions, your problem breaks down into the following steps 1) Decompose the date into YYYY, MM, DD components 2) Add 2 to MM 3) If MM > 12 then YYYY = YYYY + 1, MM = MM - 12 4) Re-assemble the date to give the first of the month following the month-end 5) Use your language's equivalent to DataAdd to get your month-end date. HTH
report
• It is ILE RPG. The returned date should be able to handle any date entered by user. A good example would be if the user entered 20050513 (today's date), it would then return 20050630. Thank you again for your great knowledge and support.
report
• In ILE RPG use the date BIF's provided. WrkDate and DateField are both defined as Date(D) data types. 1. Determine the first of the current month. WrkDate = DateField - %Days(%SubDt(DateField:*days) - 1) 2. Add two months, then subtact 1 day (the order is important), this will give you the end of the next month. WrkDate = WrkDate + %Months(2) - %Days(1) you can combine this into one expression if you like. By using the IBM functions you will not need to worry about leap year because the system will handle it. Mike
report
• And Gail wrote in with this code. Just change the date format to suit your needs, she says. -- Michelle Davidson, editor, Search400.com --------------------------------------------------------------------------------------------------------------------------------------------- * Determine Last Day of Month D DateIn S D DATFMT(*MDY) D DS D DateEnd D DATFMT(*ISO) D DateDay 2 OVERLAY(DateEnd:9) C* * Pass in Date Parameter (format is mmddyy) C* C *ENTRY PLIST C PARM BDX 6 C* * Calculate Number of Days in Month C* C MOVE BDX DATE6 6 0 C *MDY MOVE DATE6 DateIn * C DateIn ADDDUR 1:*M DateEnd C EVAL DateDay = '01' * DateEnd = End of Current Month C SUBDUR 1:*D DateEnd * DateEnd = End of Next Month C ADDDUR 1*M DateEnd
report
• Tony Cole also sent in a suggestion and code. -- Michelle Davidson, editor, Search400.com DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++ DTESTDATE S D D WRKDATE S 6 D DOM S 2 0 CL0N01Factor1++++++++++Opcode&ExtFactor2++++++++++++++Result++++++++Len++D+HiLoEq C *MDY0 MOVE WRKDATE TESTDATE C EXTRCT TESTDATE:*D DOM C EVAL DOM = (DOM - 1) C SUBDUR DOM:*DAYS TESTDATE C ADDDUR 2:*MONTHS TESTDATE C SUBDUR 1:*DAYS TESTDATE WRKDATE will be the date the user will enter in and should be a valid date in MMDDYY format. EX. WRKDATE = 102504 (MMDDYY) Move WRKDATE into date field TESTDATE. EX. TESTDATE = 102504 (MMDDYY) EXTRCT - will put the days of the month into DOM. EX. DOM = 25 Subtract one day from DOM. EX. DOM = 24 SUBDUR - subtracts the number of days from TESTDATE. This will give you the first of the month. EX. TESTDATE = 100104 (MMDDYY) ADDDUR - will add 2 months to testdate. Now you have the first of the month two months ahead. EX. TESTDATE = 120104 (MMDDYY) SUBDUR - will sub 1 day from TESTDATE and you now have the the last day of the month. EX TESTDATE = 113004 (MMDDYY) So now TESTDATE will have Nov. 30 2004, which is the end of the month. Since TESTDATE is a date field and using SUBDUR, ADDDUR this will handle leap years
report
• The code that I've posted on behalf of people is not posting correctly. If you would like the original code, please send me an email at editor@search400.com. Sorry for the inconvenience. Michelle Davidson Editor Search400.com
report
• To make it easier to view the code users sent in, I've posted the samples in a separate document on Search400.com -- http://www.search400.com/tip/1,289483,sid3_gci1089668,00.html -- Michelle Davidson, editor, Search400.com
report
• Review this in STRSQL:
```select
date((varchar(year(date('2009-10-30') + 2 months)) concat
'-' concat
right('0' concat
varchar(month(date('2009-10-30') + 2 months)),2)
concat '-01')
) - 1 day
from sysibm/sysdummy1
```
Or this variation:
```select
date((varchar(year( current date + 2 months)) concat
'-' concat
right('0' concat
varchar(month( current date + 2 months)),2)
concat '-01')
) - 1 day
from sysibm/sysdummy1
```
The code can also be adapted to the VALUES ... INTO format. The first form can be used in embedded SQL in all languages if the SQL Dev Kit is installed. It can be used in a REXX proc even if the SQL Dev Kit is not installed. In current releases, the VALUES ... INTO form should also work in REXX, but there seems to be a bug that IBM is working on and hasn't resolved yet -- it doesn't work properly at V5R4 on a test system. Tom