Re: SQL statement for last day of the month (II) - t.txt [1/1]

Very good, but don't use it after the 28th of any month -- it is not reliable. This is because if you add 1 UNITS MONTH to, for example, the 31st January, the resulting date (31st February) is recognized as invalid and an error occurs. Do not mix INTERVAL {YEAR|MONTH} TO {YEAR|MONTH} values (eg 1 UNITS MONTH) with days -- the computations are not reliable. Note that regardless of what resolution mechanism is used to resolve DATETIME(1996-01-31) + 1 UNITS MONTH, subtracting 1 UNITS MONTH from the result would not end up with the same value as you started out with. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: bmiller@neonramp.com (bmiller) }Date: 13 Feb 1996 04:27:03 GMT }X-Informix-List-Id: <news.21188> } }The following is a pure SQL solution -- no (3gl, 4gl nor host) procedure calls: } }OUTPUT: }------------------------------------------------------------------------------ } }SELECT TODAY AS current_date } ,MDY( MONTH( TODAY ), 1, YEAR( TODAY ) ) AS first_day_of_mo } ,MDY( MONTH( TODAY ), 1, YEAR( TODAY ) ) } + 1 UNITS MONTH - 1 UNITS DAY AS last_day_of_mo } ,DATE( MDY( MONTH( TODAY ), 1, YEAR( TODAY ) ) } + 1 UNITS MONTH - 1 UNITS DAY ) AS last_day_of_mo2 } } ,DATE( '12/3/95' ) AS dec_date } ,MDY( MONTH( DATE( '12/3/95') ), } 1, } YEAR( DATE( '12/3/95') ) ) AS first_day_of_dec } } ,MDY( MONTH( DATE( '12/3/95') ), } 1, } YEAR( DATE( '12/3/95') ) ) } + 1 UNITS MONTH - 1 UNITS DAY AS last_day_of_dec } } ,DATE( MDY( MONTH( DATE( '12/3/95') ), } 1, } YEAR( DATE( '12/3/95') ) ) } + 1 UNITS MONTH - 1 UNITS DAY ) AS last_day_of_dec2 }FROM informix.systables }WHERE tabid = 1; } }current_date 02/12/1996 }first_day_of_mo 02/01/1996 }last_day_of_mo 1996-02-29 }last_day_of_mo2 02/29/1996 }dec_date 12/03/1995 }first_day_of_dec 12/01/1995 }last_day_of_dec 1995-12-31 }last_day_of_dec2 12/31/1995 } }------------------------------------------------------------------------------ } }By re-casting the date result to date [DATE()] the format matches the }first_day format in the interactive SQL (dbacces & isql) output. }(I am new to INFORMIX, so there maybe other INFORMIX short-cuts or FORMAT }variables that I am unaware of.) }