Re: Query-Question with 4Y2M
On Tue, 13 Oct 1998, Colin M McGrath wrote:
> On Tue, 13 Oct 1998 Jonathan Leffler wrote:
> > 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 [...]
> >
> > 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:
>
> Any way to get a mmdd format via SQL when the month # < 10?
>
> SELECT MONTH(today - 13) * 100 + DAY(today - 13) AS mmdd
> FROM systables where tabid = 1;
>
> Result: mmdd
>
> 930
Of course -- with a stored procedure. Probably not otherwise, unless you
have IUS and use coercions and so on, and even then a stored procedure
would be cleaner (and simpler).
CREATE PROCEDURE mmdd(d DATE) RETURNING CHAR(4); DEFINE n INTEGER;
DEFINE s CHAR(5);
LET n = MONTH(d) * 100 + DAY(d) + 10000;
LET s = n;
RETURN s[2,5];
END PROCEDURE;
I've tested this one. You might be able to compress the computation
and assignment to s into one line, removing the variable n.
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