RE: SQL insert - all fields

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

There has to be a better way, but following your logic:

INSERT INTO OLITMMST
  (SELECT A.*,
         (CURRENT_DATE + 1 year + 1 month + 1 day) - DATE('0001-01-01') 
   FROM DMITMMST A
   WHERE ACREC = 'D');

Works?

Elvis

-----Original Message-----
 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?