Re: Date conversion - how?
On Mon, 17 Aug 1998, Troy Peterson wrote:
> I am trying to convert dates from an ISAM database in the form of "yyyymmdd"
> into a DATE type column in my relational database using just triggers and
> stored procedures.
>
> The closest thing I found was the TO_DATE function, but it won't accept a
> numeric month field as part of the string.
>
> Is there another approach that I'm not finding in any of the documentation?
I assume that the yyyymmdd data is in a CHAR field inside your database;
if it was still in the ISAM database, then you wouldn't be in the running
for a conversion using just triggers and SPL.
Given that SPL is an option, you should be able to do:
CREATE PROCEDURE cvt_date(old CHAR(8)) RETURNING DATE; DEFINE str CHAR(10);
DEFINE dt DATETIME YEAR TO DAY;
LET str = old[1,4] || "-" || old[5,6] || "-" || old[7,8];
LET dt = str; { Avoids problems with DBDATE values! }
RETURN dt;
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