Re: Posting from the Informix-list

Blacker, Herb wrote: > > I have an ASCII pipe-delimited file from another database which contains > dates that have had no error checking done on them (don't even ask!). > The result is that there are invalid dates in the date field. Have any > of y'all done any after-the-fact file manipulation to determine invalid > dates? What we'd like to do is substitute a single valid default date > for all invalids. I'm looking for a 'C', awk or sed program to do this. > > Thanks in advance, > Herb Blacker > Database Administrator > hhblack@prninc.com You might like to look at the Informix SET VIOLATIONS facility in version 7.1+. You could use it like this: Load your dubious dates into a char column. Set violations etc Alter the column to type date Look in the violations tables, which will identify the problem rows. Write an update statement to set the dates, using the row identification in the violations table. I haven't tested this - I'm not sure that the violations facility works when changing the column type. If not, try creating a check constraint that does a date conversion - check constraints do work. Good luck. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ ---