RE: Wrong date

["Wilt, Charles" <WiltC@xxxxxxxxxx>]

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
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To 
post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/rpg400-l.




This e-mail transmission contains information that is intended to be 
confidential and privileged.  If you receive this e-mail and you are not a 
named addressee you are hereby notified that you are not authorized to read, 
print, retain, copy or disseminate this communication without the consent of 
the sender and that doing so is prohibited and may be unlawful.  Please reply 
to the message immediately by informing the sender that the message was 
misdirected.  After replying, please delete and otherwise erase it and any 
attachments from your computer system.  Your assistance in correcting this 
error is appreciated.