RE: SQL insert - all fields

Jeff,

You owe me a beer...

create function CvtDateToNum (inDate date)       
    returns decimal(8,0)                                
language SQL                                            
not fenced                                              
deterministic no external action                        
returns null on null input                              
contains SQL                                            
begin                                                   
  return dec(substr(char(inDate,ISO),1,4)               
                    concat substr(char(inDate,ISO),6,2) 
                    concat substr(char(inDate,ISO),9,2) 
             ,8,0);                                     
end                                                     
Then to use:

INSERT INTO OLITMMST (SELECT A.*, CvtDateToNum(CURRENT_DATE)
                      FROM DMITMMST A 
                      WHERE ACREC ='D');


HTH,

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 Jeff Crosby
> Sent: Thursday, June 16, 2005 3:19 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL insert - all fields
> 
> 
> > Do yourself a favor and create a UDF to handle the conversion.
> 
> Oh crap.  Something else to learn. <vbg>
> 
> Thanks, I'll figure it out.
> 
> -- 
> Jeff Crosby
> Dilgard Frozen Foods, Inc.
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
> 
> The opinions expressed are my own and not necessarily the 
> opinion of my
> company.  Unless I say so.
> 
> 
> 
> -- 
> 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.
> 
>