RE: SQL insert - all fields

Do yourself a favor and create a UDF to handle the conversion.

If you chose to use SQL to write the UDF, the perhaps the following may be of 
use:

select dec(substr(char(current_date,ISO),1,4)         
           concat substr(char(current_date,ISO),6,2)  
           concat substr(char(current_date,ISO),9,2)) 
from sysibm/sysdummy1                                 


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 2:51 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL insert - all fields
> 
> 
> > INSERT INTO OLITMMST (SELECT A.*, CURRENT_DATE 
> >                       FROM DMITMMST A 
> >                       WHERE ACREC ='D');
> 
> Arrgh!  I forgot that DLDATE is a "legacy" date in the form 
> YYYYMMDD.  (I
> must have Y2K'ed it before the date data type was available.)
> 
> In any case, the following _almost_ worked
> 
> INSERT INTO OLITMMST
>               (SELECT A.*,
>               CURRENT DATE - CAST('0001-01-01' AS DATE)
>               FROM DMITMMST A
>               WHERE ACREC = 'D'); 
> 
> Except DLDATE is 20040515, ie, off by 1 year, 1 month, and 1 
> day.  Trying to
> CAST 0000-00-01 doesn't work.  And you can't CAST a date as 
> decimal.  There
> must be a way around this isn't there?  Get today's date as YYYYMMDD?
> 
> -- 
> Jeff Crosby
> Dilgard Frozen Foods, Inc.
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
>