Re: Wrong date

I can also use the function to find out wich records are wrong.

The whenever solution will work if it is in a program.

Wilt, Charles wrote:

You can't continue with the SELECT by itself.

You could continue if you embed the SELECT in a stored procedure with the 
appropriate error handling.

The other option is to do the date conversion in a UDF with appropriate error 
handling that for
instance handles invalid dates by returning NULL.

Here's an example UDF:

create function CvtNumISOtoDate(indte Numeric(8,0)) returns Date language SQL not fenced deterministic no external action returns null on null input contains SQL set option datfmt=*ISO begin declare INVALID_DATETIME condition for '22007'; declare exit handler for INVALID_DATETIME begin resignal sqlstate '01HD1' set message_text = 'Invalid date/time replaced by NULL'; return NULL; end; return( case indte when 0 then NULL when 99999999 then date('9999-12-31') else date(substr(digits(indte),1,4) concat '-' concat substr(digits(indte),5,2) concat '-' concat substr(digits(indte),7,2)) end ); end;

HTH,
Charles


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On 
Behalf Of Raul A. Jager
W.
Sent: Tuesday, February 20, 2007 3:52 PM
To: RPG programming on the AS400 / iSeries
Subject: Wrong date

I have a text file with a date in a fixed position, I need to convert as much 
as possible from the

file to a table. This works almost fine:

SELECT decimal(substring(txt, 5, 7)) as codigo, cast(substring(txt, 84, 4) || 
'-' || substring(txt,
88, 2) || '-' || substring(txt, 90, 2) as date) as fecha FROM libr.file

It goes Ok until it finds a wrong date. (19570229 is the first problem) this is 
in record 2,035, the

SQL gives a SQL0180 and it stops. It will be Ok to skip the record.

Is there a way to tell SQL to skip the wrong date? or a way to select all 
records with wron dates?
It is a one time convertion.

TIA
--