Re: Datetime conversion problems

>From: uunet!cdin-1.compu.com!mark (Mark Heringslake) >Date: Tue, 23 Mar 93 8:48:39 EST >Subject: <No Subject Supplied> >X-Informix-List-Id: <list.2039> >To any and all Informix guru's, et al...... > H e l p , H e l p , H E L P ! ! ! ! It's not that desparate, honest guv! And we'd answer even without that sort of hinting that it is an urgent problem. > I am running Informix 4GL SE release 4.10.UC2 and trying to convert data >from an older version of Informix. > One of the fields I need to convert is a CHAR(15) containing info similar >to a DateTime type field into a field designated as DateTime YEAR TO MINUTE. >The problem is that the CHAR(15) data is not consistant and doesn't always >convert properly. When this happens, I get a > Form Error -1262 >which is expected. Unfortunately, I do not seem to be able to TRAP this error. >I've checked STATUS and sqlca.sqlcode - both are ZERO. WHENEVER ERROR >statements don't seem to help any. If I can trap the fact that the data >doesn't convert on the 1st try, there are some things I can try to massage the >data better and try another conversion. I'm stuck. Questions..... > 1) Is there any way to trap the error so Informix doesn't display it? No. > 2) Is there something else I can check for errors? No. You could try using WHENEVER ANY ERROR, but I don't think it'll help. (If it does help, you still look at STATUS. And be very careful: about the only thing you can safely do after the conversion is assign STATUS to your own variable, and look at your own variable, because under ANY ERROR, STATUS is very volatile!) > 3) Is there another way to test the data BEFORE trying to convert into > the DateTime field? You can test whether your CHAR(15) field matches the string below before you try the assignment to datetime. "19[0-9][0-9]-[01][0-9]-[0-3][0-9] [012][0-9]:[0-5][0-9]" If it does, then there is a very good chance it'll convert correctly. If it doesn't, take a good look at it and convert it until it does match. If your dates encompass the 19th or 21st century, you'll obviously need to upgrade the first two characters of the matches string. You'll probably find that the datetime conversion forgives single digit months and days, and maybe hours but not minutes. I also think that CHAR(15) is not long enough to hold "yyyy-mm-dd hh:mm"; that needs CHAR(16). Is that the cause of some of your trouble? You might also want to consider unloading the existing data, using sed or awk to massage the field to be converted, then reload the data, then do the conversion in I4GL, but this time you know that the data will all convert OK. Finally, you might want to consider using a C function to do the conversion. There again, you might find it easier not to, especially since you probably don't have the 5.00 documentation to help you. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h> PS: If the old software is in use, then make sure it IS generating consistent and correct data before you do this.