RE: SQL0181 and the IBM Date Window...

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.