Re: Last Date of previous month

Jack Parker wrote: > get current year and month > set day element of that to 1 (i.e. the first) > subtract 1 day from that > viola - the last day of last month. 'cello. Michael Krzepkowski gave a neat formulation which is also moderately efficient since it uses DATE values and integers instead of converting to DATETIME and doing interval arithmetic and converting back. > The major flaw in this approach is that you are limited to running for ONLY > last month, if you wish to run a report for two months ago your code won't > handle it. It's never a good idea to put date specification logic like this > into the query - always better to pass it in as a parameter - which you can > do with ace. I would move the date logic out to the OS and not make ace > worry about it. The first half of this observation is absolutely valid - sooner or later you'll need to run the report for September in November. It is not wholly unreasonable to put the date specification into the SQL of a report - but it is a good idea to put an effective date as a parameter and (where possible) default that to TODAY. So, for this particular report, it sounds as if you could do with a stored procedure that takes a date and returns the last day of the prior month. CREATE PROCEDURE last_day_prior_month(d1 DATE DEFAULT TODAY) RETURNING DATE; DEFINE d2 DATE; LET d2 = MDY(MONTH(d1), 1, YEAR(d1)) - 1; RETURN d2; END PROCEDURE; That's not been past a server, so there could be a syntax error (or worse) in it, but I think it is OK. You could compress it to a single line in the body of the function "RETURN MDY(MONTH(d1), 1, YEAR(d1)) - 1;", but it might be marginally easier to debug this way. Now, you could run the report using a parameter which is some day in the month after the one you want the report for, but that's apt to be a bit messy logically (so maybe Jack is right after all). If I was designing the report, it would probably take 'some date in the month for which you want the report run', and would calculate the first and last days of that month. You do then end updoing sme calculation out in the shell. In I4GL, I'd have different options on the command line; for example, no option means use use month prior to today, but using -e 2003-09-10 would produce a report for September 2003 (as would 29 other distinct dates). You can't quite manage that in ACE alone - you'd have to have a shell script to drive it - back to Jack's comment. >"navdeep virk" <nvirk@msn.com> wrote: >> I want to obtain the Last Date of previous Month ie 09/30/2003, >> if report is run in Oct . and 10/31/2003 when report is run in >> Nov. I need a Sql solution as I will be using an ACE report. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/