x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 706

# 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;
``````
0
Jim Horn
• 2
1 Solution

Commented:
wrap what you're assigning dtEnd on line 17 in LAST_DAY(), it takes a date or datetime argument.

``````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 = LAST_DAY(to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-07"))
end if;
``````

http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_1538.htm
0

Microsoft SQL Server Developer, Architect, and AuthorAuthor Commented:
... looking ...
0

Billing EngineerCommented:
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.
0

Microsoft SQL Server Developer, Architect, and AuthorAuthor Commented:
I moved on to a different gig and can't verify this, but I suspect this is the correct answer.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.