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)) || "");
Originally posted by dananio
You can use '::' to casting datatype
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