Here's my first attempt. I get a 'datatype unknown' error when the
update is executed. The variable :mbrbirthnew is a valid YYYY-MM-DD.
If I get rid of the CAST it complains that '1992-07-01' isn't a valid
date.

DECLARE VARIABLE rxclaimnbr VARCHAR(15);
DECLARE VARIABLE clmseqnbr CHAR(3);
DECLARE VARIABLE claimsts CHAR(1);
DECLARE VARIABLE mbrbirthold VARCHAR(8);
DECLARE VARIABLE mbrbirthnew VARCHAR(10);
begin
FOR SELECT RXCLAIMNBR,CLMSEQNBR,CLAIMSTS,MBRBIRTH
FROM X_CLAIM_DATES
INTO :rxclaimnbr,:clmseqnbr,:claimsts,:mbrbirthold
DO
BEGIN
IF (mbrbirthold NOT CONTAINING '000000') THEN
BEGIN
mbrbirthnew = SUBSTR(:mbrbirthold,1,4) || '-' ||
SUBSTR(:mbrbirthold,5,6) || '-' || SUBSTR(:mbrbirthold,7,8);
UPDATE CLAIMSRAW
SET MBRBIRTH=CAST(:mbrbirthnew AS DATE)
WHERE RXCLAIMNBR=:rxclaimnbr AND CLMSEQNBR=:clmseqnbr AND
CLAIMSTS=:claimsts;
END
END
end
-----Original Message-----
From: Rick DeBay
Sent: Monday, July 26, 2004 11:32 AM
To: 'firebird-support@yahoogroups.com'
Subject: Importing and parsing data in stored procedure

I have a table that contains dates and times, but they're stored as
varchar in either yyyymmdd, yymmdd, or hhmmss format. I'm importing
them to another table so I can correctly store them as a Date or Time.
Can I do this with a stored procedure? If so, can someone let me know
what I need to do to parse the fields?

Rick DeBay
Senior Software Developer
RxStrategies.net