Input file has date as 02202006 named START_MDYY. I attempt to reformat it to 2006-02-20 using DEFINE to prepare it for bcp load into a sql server table.

DEFINE FILE CECACCPR

YYYY /A4 = EDIT(START_MDYY,'$$$$9999');
MM /A2 = EDIT(START_MDYY,'99$$$$$$');
DD /A2 = EDIT(START_MDYY,'$$99$$$$');
START_MDYY_A10 /A10 = YYYY | '-' | MM | '-' | DD;

The resulting value in the bcp file is 8402-00-03. NOT GOOD. I’m new and appreciate any advice you can offer as to what is happening and how to correct it. Thanks, rvb

This message has been edited. Last edited by: Kerry,

It looks like you are trying to use EDIT on a Date formated field. That will produce strange results.

Try the DATECVT function, i.e.:

 
DEFINE FILE CAR
  START_MDYY/MDYY WITH COUNTRY = &MDYY;
  START_DATE_YYMD/YYMD = DATECVT(START_MDYY, 'MDYY', 'YYMD');
  START_DATE_2/A10 = DATECVT(START_DATE_YYMD, 'YYMD', 'A8YYMD');
END

TABLE FILE CAR
PRINT
  START_MDYY
  START_DATE_YYMD
  START_DATE_2
WHERE READLIMIT EQ 1
END
 

Thanks for the reply and the nice example. When I try it my TABLE FILE fails because it does not recognize START_DATE_2? There must be other issues that I'm not seeing. Will your code example put a - between the date values? Like this YYYY-MM-DD.

What format is START_MDYY ?

Hi Rob,

I added an extra line to robert's previous code. Here is the example:

DEFINE FILE CAR
START_MDYY/MDYY WITH COUNTRY = &MDYY;
START_DATE_YYMD/YYMD = DATECVT(START_MDYY, 'MDYY', 'YYMD');
START_DATE_2/A10 = DATECVT(START_DATE_YYMD, 'YYMD', 'A8YYMD');
START_DATE_REDEFINE/A10 = EDIT(START_DATE_2, '9999-99-99' );
END

TABLE FILE CAR
PRINT
START_MDYY
START_DATE_YYMD
START_DATE_2
START_DATE_REDEFINE
WHERE READLIMIT EQ 1
END

Hope this helps!

Regards,
Venkat

in the .mas it is MDYY

FIELD=START_MDYY ,START_MDYY
,MDYY ,A8
,MISSING=ON
,$

I'm considering changing MDYY to A8 to see if the EDIT returns what I need. At this point I don't see why I can't treat the input file value of 02202006 as A8 so I can use EDIT. Please advise if I'm looking for trouble. Thank you Waz for replying.

Thank you Venkat, for showing me how to get the - into the field using another EDIT. I will try it.

Is this the report output ?

Can you just use

TABLE FILE CAR
PRINT COUNTRY
COMPUTE START_MDYY/MDYY = &MDYY ;
COMPUTE START_YYMD/YY-M-D = START_MDYY ;
END

I changed the .mas to define START_MDYY as A8 instead of MDYY. This allowed the my original EDIT logic to work as expected (MAJOR thank you to rfbowley for the clue that: EDIT on a Date formated field will produce strange results). That really got me thinking that there was no logical reason that this field needs to be defined as MDYY in my case.

Waz, thanks for the example using compute with YY-M-D. Thank you (all responders) for helping to open my mind to WF coding techinques that I'm lacking and now learning thanks to YOU. Sincerly, rvb

Thanks for giving something back, as we spend our own time answering and suggesting solutions.

Makes me feel like it is worthwhile posting on the forum.