Re: Date field problem --- old version

David Williams <djw@smooth1.demon.co.uk> wrote: >In article <6a10np$oio@bgtnsc01.worldnet.att.net>, Jay Aymond ><jay@ulife.com> writes >>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". >> > Try setting the environment variable DBDATE. > > I normally use > > DBDATE=DMY4/; export DBDATE> > > If this fails you will need to > > a) Run dbschema -d <mydatabase> -t <mytable> > myfile.sql > > b) Edit myfile.sql and change the <mytable> to <mytable2> > then run this in isql. This will create a new table with the > same columns as the old table. > > c) Write and run a 4gl program like: > > DATABASE mydatabase > > MAIN > > DEFINE t1_rec RECORD LIKE mytable.* > DEFINE mystatus INTEGER > > DECLARE QC_1 CURSOR FOR SELECT * FROM mytable > > OPEN QC_1 > LET mystatus = 0 > > WHENEVER ERROR CONTINUE > > WHILE (mystatus = 0) OR (mystatus = -1213) > > FETCH QC_1 INTO t1_rec > LET mystatus = status > > IF mystatus = 0 THEN > INSERT INTO mytable2 value (t1_rec.*) > LET mystatus = status > END IF > END WHILE> > CLOSE QC_1 > END MAIN > > > d) DROP TABLE <mytable> > > e) RENAME <mytable2> to <mytable> > > >>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? >> >> >> >> >>___________________________________________________________ >>Jay Aymond >>United Life & Annuity Insurance Company >> >>Email: jay@ulife.com > >-- >David Williams > >Maintainer of the Informix FAQ > Primary site (Beta Version) http://www.smooth1.demon.co.uk > Official site http://www.iiug.org/techinfo/faq/faq_top.html > >I see you standin', Standin' on your own, It's such a lonely place for you, For >you to be If you need a shoulder, Or if you need a friend, I'll be here >standing, Until the bitter end... >So don't chastise me Or think I, I mean you harm... >All I ever wanted Was for you To know that I care Thanks for the suggestion, but the machine does not have 4GL on it --- just ISQL. I think I can exclude the corrupted records by selecting only a range of dates. ___________________________________________________________ Jay Aymond United Life & Annuity Insurance Company Email: jay@ulife.com