Hello, all

Is there any procedure or function or package in informix I can use to convert a date type to int or string in dbaccess?

for instance,

table a
col1 serial
col2 date

select <function_name>(col2) from a;

Could anyone help, thanks.

I assume you want to convert datetime to UNIX Timet value? If so try this...

NOTE: Function will work for IDS but not SE

CREATE FUNCTION spl_datetime_to_unixtimet(
pDatetime DATETIME YEAR TO SECOND
) RETURNING INT;

DEFINE vUnixTimet INTEGER;

-- The maximum Informix second interval precision is 9 digits which is too small
-- to hold the number of seconds since 1970... Therefore, get the number of seconds
-- since 2000-01-01 00:00:00 and add 946684800 to which represents the number
-- of seconds from 1970 to 2000...
--
LET vUnixTimet = ((INTERVAL(0) SECOND(9) TO SECOND
+ (pDatetime - DATETIME(2000-01-01 00:00:00) YEAR TO SECOND)) || "");

RETURN (vUnixTimet + 946684800);
END FUNCTION;

You can use '::' to casting datatype
such as
SELECT aa::char(10)

Unfortunately, I am using SE not IDS.
Actually, I want to migrate SE to IDS, but seems some date data corrupted. I can't export SE data, it reported 1210 error. I prefer to convert the date data to a int or string type so that I can correct it because I can't directly retreive these corrupted date.

select col1 where col2<'01/01/1000' got a 1210 error.
for example, if there is a routine To_Int(DATE) which can convert DATE to INT, then maybe I can do in this way,
select col1 where To_Int(col2) < 0