Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

What's the informix SQL for end of month?

Hi All

I'm butchering my way through creating my first Informix procedure (SQL expert, Informix N00b) and I'm passing a year and month as integers.

Question:  Given year and month number, what's the function for automagically determining the last day of that year-month?  For example, year=2010 and month=9 should return the date 2010-09-30.  Should go in line 17 of the below code block.

Thanks.
Jim

CREATE PROCEDURE informix.sales_distribution(iYear int, iMonth int, iWeekNumber int)

define dtStart DATETIME YEAR TO DAY;
define dtEnd DATETIME YEAR TO DAY;

if iWeekNumber = 1 then
   let dtStart = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-01") 
   let dtEnd = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-07")
elseif iWeekNumber = 2 then 
   let dtStart = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-08") 
   let dtEnd = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-14")
elseif iWeekNumber = 3 then
   let dtStart = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-15") 
   let dtEnd = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-21")
elseif iWeekNumber = 4 then
   let dtStart = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-22") 
   let dtEnd = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-07")   -- ????
end if;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Dulton
Dulton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn

ASKER

... looking ...
I've requested that this question be closed as follows:

Accepted answer: 500 points for Dulton's comment #a39983835

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
I moved on to a different gig and can't verify this, but I suspect this is the correct answer.  Thanks.