Re: Query-Question with 4Y2M

On Tue, 13 Oct 1998, Jonathan Leffler <jleffler@informix.com> >On Tue, 13 Oct 1998 andy@pneu.com wrote: >>I search for a way in sql to get an output year and month like >>that: yyyymm where mm must have an leading zero for months < 10. >>column-type is date. >> >>My idea: select YEAR(today) && MONTH(today) from anytable >>does not work, the zero is missing..... >>In ace and 4gl, i would use "using....", but in sql? >>Any Ideas (without changing any DBDATE-settings) ? > >Someone else asked a minor variant on this question not so very long ago. >The answer is simple: > > SELECT YEAR(today) * 100 + MONTH(today) AS yyyymm > FROM AnyTable > >It gives you a 6-digit integer, just as you wanted. You might want to have >a stored procedure to do the job rather than write the expression out many >times: > > CREATE PROCEDURE yyyymm(d DATE) RETURNING INTEGER;> RETURN YEAR(d) * 100 + MONTH(d); > END PROCEDURE; Drat -- I didn't test that before sending it; I was confident it would work. I was wrong. It generates a syntax error. The correct SPL is: CREATE PROCEDURE yyyymm(d DATE) RETURNING INTEGER; RETURN (YEAR(d) * 100 + MONTH(d)); END PROCEDURE; There's an extra pair of parentheses around the expression which is returned. Non; je ne comprend pas! Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn