Re: Wrong date

["Raul A. Jager W." <raul@xxxxxxxxxx>]

Thank you, it is working. 
It looks it will take some time because the file has a lot of records. 
One time deal, so I use interactive SQL.
I can also use the function to find out wich records are wrong.

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

Raul  
____________________________________________________________________
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
> --