How to calculate specific previous date based on today's date - Forum - Community Groups - Progress Community

How to calculate specific previous date based on today's date

 Forum

How to calculate specific previous date based on today's date

  • Hi,

      I am just getting back into progress after 4 year layoff....I am trying to figure out the following for a report, using 10.1C

    I need to determine the last day of the month which is 3 months prior to todays date

    Example: Today is July 1, 2013. So I need to bring in the last day of April. So current month minus 2 - then first day of that month minus 1.

    thanks

  • Here is a working example - based on MM/DD/YY format. You can change to your convention as needed.

    It's a PITA as you have to allow for calc in months 1 and 2 and separate your logic.

    There way be a zippy nifty way to do this using SQL dates, so if someone has an example, great.

    But this works.

    define variable newdate# as date no-undo.

    define variable tempchar# as character no-undo.

    define variable month# as integer no-undo.

    define variable day# as integer no-undo.

    define variable year# as integer no-undo.

    define variable newmonth# as integer no-undo.

    define variable newday# as integer no-undo.

    define variable newyear# as integer no-undo.

    assign

    month# = integer(month (today))

    day# = integer(day (today))

    year# = integer(year (today)).

    if month#

    assign

    newmonth# = month# + 12 - 2 /* yes -10 is what we need but this helps figure out what we're doing */

    newyear# = year# - 1.

    else

    assign

    newmonth# = month# - 2

    newyear# = year#.

    tempchar# = string(newmonth#) + "/01/" + string(newyear#).

    newdate# = date(tempchar#) - 1.

    message tempchar# newdate#

    view-as alert-box.

  • Depending on your version, you can also look at ADD-INTERVAL(). You can say

    ADD-INTERVAL(now, -1, 'months'),

    -- peter

  • And then work your way back to the last day of the previous month:

    DEFINE VARIABLE dDate AS DATE NO-UNDO.

    dDate = ADD-INTERVAL(now, -1, 'months').

    dDate = dDate - DAY(dDate).

  • Steps which aren't affected by -d (date format):

    1. Using today, find the beginning of the month.

    2. Take off the requisite amount of months.

    3. As you are at the beginning of the month, it is one day before.

    Or all in one go ...

    MESSAGE ADD-INTERVAL(ADD-INTERVAL(DATE(MONTH(TODAY), 1, YEAR(TODAY)), -3, 'month'), -1, 'day')

  • The last option worked perfectly...thanks to all who responded!