character to date conversion

How can I convert dates held as character strings in the format 1991202 (ie 02 dec 1999) to dates? Or alternatively can I manipulate these character strings so they behave as dates in queries? I would prefer to leave the data in the table unchanged. MMany thanks in advance Graeme Muirhead g_muirhead@csisystems.co.uk

Graeme Muirhead wrote: > How can I convert dates held as character strings in the format 1991202 (ie > 02 dec 1999) to dates? Or alternatively can I manipulate these character > strings so they behave as dates in queries? I would prefer to leave the data > in the table unchanged. Where do these date strings come from? The chances are that DATE('2 dec 1999') will work OK provided Dec is a correct month abbreviation in your locale and your DBDATE setting has year last - not guaranteed, but likely. Handling the other string is error prone unless you use DBDATE="Y4DM-"! If you use the more normal DBDATE="Y4MD-", you'd get an invalid month in date error, because of the typo in the string (1991-20-02 is the only date that would be parsed from what's written). Note that you could not interpret both strings successfully in a single program. Consequently, it becomes important to know where the info is coming from. Is this a programming language or user-entered data, or what? You would be able to use MDY(str[5,6], str[7,8], str[1,4]) where str contains 19991220. You might have other options too, but it depends... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>