Re: Date field problem --- old version

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