Re: character to date conversion
Looks to me the the date is in a CYYMMDD format, rather than a typo.
Can't see how you can do the conversion without a stored procedure, something like:
CREATE PROCEDURE sp_cyymmdd2date(p_date CHAR(7)) RETURNING DATE;
DEFINE l_int INTEGER;
DEFINE l_str CHAR(8);
DEFINE l_date DATE;
LET l_int = p_date;
LET l_int = l_int + 18000000;
LET l_str = l_int;
LET l_date = MDY(l_str[5,6], l_str[7,8], l_str[1,4]);
RETURN l_date;
END PROCEDURE;
Then you could do: select sp_cyymmdd2date(mydate) from mytable. Should slow things down.
Personally, I'd change the tables - a 4 byte date has got to be better than a 7 byte char ...
(could also do: select (mdy("mm", "dd", "cyy") + 1800 units year) ... , but it won't work for leap-years)
>>> Jonathan Leffler <jleffler@earthlink.net> 02/29/00 06:35am >>>
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>