Jim Horn
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
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
I moved on to a different gig and can't verify this, but I suspect this is the correct answer. Thanks.
ASKER