Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    6

    Unhappy Unanswered: date function or package

    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.

  2. #2
    Join Date
    May 2003
    Posts
    3
    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;

  3. #3
    Join Date
    Mar 2002
    Posts
    112

    re

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

  4. #4
    Join Date
    Jun 2003
    Posts
    6

    Re: re

    Originally posted by dananio
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •