Re: Date field problem --- old version

Jay Aymond wrote: > > I have a user that is running Informix SE 2.10 on AIX --- I know this is a very > old version. > > I am trying to unload a table that has about 400,000 rows in it so that it can > be moved into another application. The table contains a couple of character > fields and three date fields. > > Some of the rows in this table have invalid data in the date fields --- I don't > know how it got there. When I use the "unload to "xxx" select * from > table_name" command, I get a "Error 1210 - Date cannot be converted to MDY > format". > > Is there some way I can avoid getting this error? > Is there some way I can update the rows with bad dates to another valid value? > Is there some undocumented function on the select statement that I can use? > If I have to read the ".dat" with an external program, how is the date field > formatted? Try: UPDATE <table> SET datefield="12/31/1899" -- This is the ZERO date WHERE datefield < "12/31/1899" OR datefield > <MAXIMUM REASONABLE DATE> If the customer has ESQL/C or 4GL and this does not work you could do the same thing by testing against -1 stored in an integer host variable. The date printout format is controlled by the DBDATE environment variable (RTM) and defaults to "MM/DD/YY". Art S. Kagel