I have a few SPs for handling date boundaries... of course they could be
improved upon I am sure... but more than welcome to change them to suit
your requirements, datatypes etc... they run on a naming convention of
AABB_CCDD where:

AA is the returned datatype: dt = datetime
BB is precision; yd = year to day, ys = year to second
CC is the position in the month, fo = First of, lo = Last of
DD is the relative month, lm = last month, tm=this month, nm=
next month.


CREATE PROCEDURE "informix".dtyd_fotm( ) RETURNING DATETIME YEAR TO DAY;

DEFINE v_dttm_yd DATETIME YEAR TO DAY;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH, YEAR TO DAY)
INTO v_dttm_yd
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_yd;

END PROCEDURE
DOCUMENT "_dt dtyd_fotm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtyd_lotm( ) RETURNING DATETIME YEAR TO DAY;

DEFINE v_dttm_yd DATETIME YEAR TO DAY;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH + 1 UNITS MONTH, YEAR TO
DAY) - 1 UNITS DAY
INTO v_dttm_yd
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_yd;

END PROCEDURE
DOCUMENT "_dt dtyd_lotm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtyd_fonm( ) RETURNING DATETIME YEAR TO DAY;

DEFINE v_dttm_yd DATETIME YEAR TO DAY;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH + 1 UNITS MONTH, YEAR TO
DAY)
INTO v_dttm_yd
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_yd;

END PROCEDURE
DOCUMENT "_dt dtyd_fonm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtyd_lonm( ) RETURNING DATETIME YEAR TO DAY;

DEFINE v_dttm_yd DATETIME YEAR TO DAY;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH + 2 UNITS MONTH, YEAR TO
DAY) - 1 UNITS DAY
INTO v_dttm_yd
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_yd;

END PROCEDURE
DOCUMENT "_dt dtyd_lonm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtyd_folm( ) RETURNING DATETIME YEAR TO DAY;

DEFINE v_dttm_yd DATETIME YEAR TO DAY;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH - 1 UNITS MONTH, YEAR TO
DAY)
INTO v_dttm_yd
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_yd;

END PROCEDURE
DOCUMENT "_dt dtyd_folm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtyd_lolm( ) RETURNING DATETIME YEAR TO DAY;

DEFINE v_dttm_yd DATETIME YEAR TO DAY;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH, YEAR TO DAY) - 1 UNITS DAY
INTO v_dttm_yd
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_yd;

END PROCEDURE
DOCUMENT "_dt dtyd_lolm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtys_fotm( ) RETURNING DATETIME YEAR TO
SECOND;

DEFINE v_dttm_ys DATETIME YEAR TO SECOND;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH, YEAR TO SECOND)
INTO v_dttm_ys
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_ys;

END PROCEDURE
DOCUMENT "_dt dtys_fotm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtys_lotm( ) RETURNING DATETIME YEAR TO
SECOND;

DEFINE v_dttm_ys DATETIME YEAR TO SECOND;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH + 1 UNITS MONTH, YEAR TO
SECOND) - 1 UNITS SECOND
INTO v_dttm_ys
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_ys;

END PROCEDURE
DOCUMENT "_dt dtys_lotm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtys_fonm( ) RETURNING DATETIME YEAR TO
SECOND;

DEFINE v_dttm_ys DATETIME YEAR TO SECOND;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH + 1 UNITS MONTH, YEAR TO
SECOND)
INTO v_dttm_ys
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_ys;

END PROCEDURE
DOCUMENT "_dt dtys_fonm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtys_lonm( ) RETURNING DATETIME YEAR TO
SECOND;

DEFINE v_dttm_ys DATETIME YEAR TO SECOND;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH + 2 UNITS MONTH, YEAR TO
SECOND) - 1 UNITS SECOND
INTO v_dttm_ys
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_ys;

END PROCEDURE
DOCUMENT "_dt dtys_lonm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtys_folm( ) RETURNING DATETIME YEAR TO
SECOND;

DEFINE v_dttm_ys DATETIME YEAR TO SECOND;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH - 1 UNITS MONTH, YEAR TO
SECOND)
INTO v_dttm_ys
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_ys;

END PROCEDURE
DOCUMENT "_dt dtys_folm(void) /*v0.0.0*/";

CREATE PROCEDURE "informix".dtys_lolm( ) RETURNING DATETIME YEAR TO
SECOND;

DEFINE v_dttm_ys DATETIME YEAR TO SECOND;

BEGIN
ON EXCEPTION
RETURN NULL;
END EXCEPTION

SELECT EXTEND( CURRENT YEAR TO MONTH, YEAR TO SECOND) - 1 UNITS
SECOND
INTO v_dttm_ys
FROM systables
WHERE tabid = 1;

END

RETURN v_dttm_ys;

END PROCEDURE
DOCUMENT "_dt dtys_lolm(void) /*v0.0.0*/";



I find them totally adequate,
HTH.


________________________________

From: Prateek Jain [mailtorateekvjain@gmail.com]
Sent: Tuesday, 9 May 2006 19:04
To: Obnoxio The Clown
Cc: informix-list@iiug.org
Subject: Re: How to get the end value of month


Thanks Obnoxio

But for the month of december its getting bombed i am using the Below
query for retrival as i have to retrive result through query only
I tried case statement but thats not working

select day(date('01' || '/' || month(current)+ 1 || '/'
||year(current))- 1 units day) from product

CASE statement query

select day(date('01' || '/' ||
CASE
WHEN month(current) = 12 THEN "11"
WHEN month(current) < 12 THEN (month(current) + 1)
END CASE || '/' ||year(current))- 1 units day) from product



On 5/9/06, Obnoxio The Clown <obnoxio@serendipita.com> wrote:


Prateek Jain said:
> Hi,
> I want to execute query for that i require end date of the
month,
>
> suppose if the month is April then it should return 30
> if March then in should return 31
> Is there any inbuilt function for that which give the end date
of the
> month

I don't think there is an inbuilt function, but you can work out
(1st day
of next month) - 1 easily enough.

--
Bye now,
Obnoxio

--
Regards,
Prateek Jain

----------------------------------------------------

This message is for the named person's use only.

Privileged/confidential information may be contained in
this message. If you are not the addressee indicated in
this message (or responsible for delivery of the message
to such person), you may not copy or deliver this
message to anyone. In such case, you should destroy
this message, and notify us immediately.

Any views expressed in this message are those of the
individual sender, except where the message states
otherwise and the sender is authorised to state them to
be the views of any such entity.
----------------------------------------------------