I'm curious to find out if anyone knows of any issues surrounding a
date field returning an error that it cannot be formatted in the
mm-dd-yyyy format. We tried to cast it to an integer (thinking that
maybe it was still okay as an integer but just won't convert) only to
receive a syntax error

SELECT CAST(date_stamp AS INTEGER) FROM tableName - returns syntax
error

It looks like there are specific records that are corrupt and won't
format into mm-dd-yyyy.

I'm unable to find any support for Informix 4.10 (go figure) and
wondered if anyone here knew offhand some steps I might take to help
get the data or if this is a common enough problem to have a
work-around.

My guess is that I do too much SQL Server and am too ignorant with
informix.

thanks in advance,
J

jeff wrote:
> I'm curious to find out if anyone knows of any issues surrounding a
> date field returning an error that it cannot be formatted in the
> mm-dd-yyyy format. We tried to cast it to an integer (thinking that
> maybe it was still okay as an integer but just won't convert) only to
> receive a syntax error
>
> SELECT CAST(date_stamp AS INTEGER) FROM tableName - returns syntax
> error

The cast notation was added to IDS 9 about 8 years ago; it was not
available in Informix 4.10, which was released about 16 years ago (and
was obsolete about 10 years ago).

> It looks like there are specific records that are corrupt and won't
> format into mm-dd-yyyy.

There are specific minimum and maximum values for dates:
enum { DATE_MIN = -693594L }; /* 0001-01-01 */
enum { DATE_MAX = +2958464L }; /* 9999-12-31 */

> I'm unable to find any support for Informix 4.10 (go figure) and
> wondered if anyone here knew offhand some steps I might take to help
> get the data or if this is a common enough problem to have a
> work-around.
>
> My guess is that I do too much SQL Server and am too ignorant with
> informix.

One way to find out whether there are problems is to run an ESQL/C or
equivalent program on the table that simply selects the date values as
stored. You probably can't effectively add conditions on the query -
you might try WHERE datecol NOT BETWEEN MDY(1,1,1) AND MDY(12,31,9999).
The type of the column will be int rather than date; you can simply
print the value - or validate whether it is in range.

You're quite right that no-one will support Informix 4.10; this was
true for quite a while before Y2K.