Hi Would anyone be able to point me in the direction of how to calculate the first and last date in a quarter based on any given date as a param
I can get the first and last date of a month using
ASSIGN dtFirstOfMonth = DATE(MONTH(dtDate),1,YEAR(dtDate))
dtLastOfMonth = dtFirstOfMonth + 31 - DAY(dtFirstOfMonth + 31).
but not sure how i would get a full quarter based on any given date ?
If you can do it in Excel you can certainly do it in ABL:
def var i_dt as date no-undo initial 3/31/2018. def var dtstart as date no-undo. def var iquarter as int no-undo. assign iquarter = truncate( ( month( i_dt ) - 1 ) / 3, 0 ) + 1 . dtstart = date( ( iquarter - 1 ) * 3 + 1, 1, year( i_dt ) ) . message iquarter skip iso-date( dtstart ) skip iso-date( add-interval( dtstart, 3, "months" ) - 1 ) view-as alert-box.
dtFirstOfMonth = dtDate - (day(dtDate) - 1).
dtLastOfMonth = add-interval(dtFirstOfMonth,1,"month") - 1.
That's probably a more elegant solution.
How would you work out the first and last dates in a quarter on paper? I'm asking because the term 'quarter' seems to vary from country to country. Also, working out how you would do it on paper is a good step to solving it in code.
If i was doing it in excel i would use this for the first date
=DATE(YEAR(A2),FLOOR(MONTH(A2)-1,3)+1,1)
and for the last date i would use
=DATE(YEAR(A2),((INT((MONTH(A2)-1)/3)+1)*3)+1,1)-1
If you can do it in Excel you can certainly do it in ABL:
def var i_dt as date no-undo initial 3/31/2018. def var dtstart as date no-undo. def var iquarter as int no-undo. assign iquarter = truncate( ( month( i_dt ) - 1 ) / 3, 0 ) + 1 . dtstart = date( ( iquarter - 1 ) * 3 + 1, 1, year( i_dt ) ) . message iquarter skip iso-date( dtstart ) skip iso-date( add-interval( dtstart, 3, "months" ) - 1 ) view-as alert-box.
Thanks Stefan, that's it !
Hi Unfortunately this doesn't seem to produce the correct results for the whole year.
for example if i enter 31/12/17 (dd-mm-yyyy) it gives qtr 3 2017-07-01 - 2017-09-30
Are you using the code from this forum or from the initial e-mail? My unedited post had the modulo and truncate wires crossed. If I use the above and use date 12/31/17 (mdy) then I get:
---------------------------
Message
---------------------------
4
2017-10-01
2017-12-31
---------------------------
OK
---------------------------
You can view this here: abldojo.services.progress.com:443/
Hi Stefan. I have it working, For some reason the iso-date doesn't work in our character abl . But i've got it working with standard date. Thanks for your help.
For a ADD-INTERVAL() option, you can also use "days" as a unit (in addition to years', ‘months', ‘weeks', ‘days', 'hours', ‘minutes', ‘seconds' or ‘milliseconds'. These values are case insensitive and may be singular per the doc/help).