RE: SQL0181 and the IBM Date Window...

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Dane,

Sorry I was out yesterday.  Here you go:

create function CvtNumJULtoDate(indte Numeric(7,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 9999999 then date('9999-12-31')                      
      else date(digits(indte))
     end                                                         
   );                                                            
end;                                                             


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dane Cox
> Sent: Friday, July 14, 2006 4:53 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL0181 and the IBM Date Window...
>
> Thanks Charles!
>
> I will try this, but did some of the text on your version get cut off?
> It looks incomplete?  
>
> This is all I see...
>
> create function CvtNumJULtoDate(indte Numeric(7,0)) returns Date 
> language SQL                                                          
> not fenced                                                            
> deterministic no external action                                      
> returns null on null input                                            
> contains SQL                                                          
> set option datfmt=*ISO                                                
>
> Thanks,
> Dane
> NOTICE: This electronic mail message and any files 
> transmitted with it are intended exclusively
> for the individual or entity to which it is addressed. The 
> message, together with any attachment, may contain 
> confidential and/or privileged
> information. Any unauthorized review, use, printing, saving, 
> copying, disclosure 
> or distribution is strictly prohibited. If you have received 
> this message in error, please immediately
> advise the sender by reply email and delete all copies.
>
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.