Re: Query-Question with 4Y2M
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;
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
============ From the archives ============
Date: Tue, 16 Dec 1997 11:40:30 -0800 (PST)
From: Jonathan Leffler <johnl@informix.com>
To: BGosalia@bloomberg.net
Subject: Re: Julian Date
X-Informix-List-Id: <list.17885>
On 16 Dec 1997, Bloomberg L.P wrote:
> As a very new user of Informix, I have no idea if there are standard
> commands in Informix that can give me the current date in Julian Date
> Format (YYYYDDD).
No, not as standard. However, it is not very hard to devise a stored
procedure which will do the job:
CREATE PROCEDURE julian_date(d DATE) RETURNING INTEGER; RETURN (YEAR(d) * 1000) + (d - MDY(1, 1, YEAR(d))) + 1;
END PROCEDURE;
Given that today is 16th December 1997, running the following yields the
answers 1997350 and 1998001 (and selecting TODAY+16 yields 01/01/1998):
SELECT julian_date(TODAY) FROM SysTables WHERE Tabid = 1;
SELECT julian_date(TODAY+16) FROM SysTables WHERE Tabid = 1;
In point of detail, you can write the expression in the SP inline anywhere
you wish to do so, so you do not have to use an SP. But I humbly suggest
that it is easier to understand the SELECT if the SP is used.
Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>