How to calculate specific previous date based on today's

Posted by BurtPaulson on 11-Jun-2013 08:36

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

All Replies

Posted by robw@hltool.com on 11-Jun-2013 16:13

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.

Posted by Peter Judge on 11-Jun-2013 16:43

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

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

-- peter

Posted by Patrick Tingen on 12-Jun-2013 02:11

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).

Posted by bootcomp on 13-Jun-2013 03:42

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')

Posted by BurtPaulson on 25-Jun-2013 10:39

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

This thread is closed