RE: How to get the end value of month

Posted in 2006

Burton, Clifford — Wed, 10 May 2006 00:24:20 +1000 — source: Usenet: comp.databases.informix

Of course if you are only interested in the actual day of the month, I doubt I need to say it, but: SELECT DAY( dtyd_lotm()) FROM systables WHERE tabid=1; Would return a single integer, being the last day of this month. -----Original Message----- From: Burton, Clifford [mailto:clifford_burton@simplus.com.au] Sent: Wednesday, 10 May 2006 00:16 To: Prateek Jain Cc: informix-list@iiug.org Subject: RE: How to get the end value of month 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