I have a field that is A40V and is used to store a date such as 04/5/09 and I need to convert it to YYMD so I can graph values by month.

The A40V field has inconsistent entry, in some cases it is 8 characters and other times only 6 (including the slashes) for example:

01/01/09 or 1/1/09

Can this be done with a define function or some other way?

I have tried searching FP and the help menu but didn't find anything that I thought was the same as this.

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

You could use GETTOK to split up the pieces. Then maybe you could play with ARGLEN to determine the significant characters to decide whether you want to add a zero to the front. After you have all the pieces set up, concatenate them together, then convert to YYMD using your favorite method. There are lots of them.

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

Here is one 'crude' way of doing what Ginny has mentioned..

 
-**From ALPHA with variable length & format to DATE. 
-**DB Format is assumed to be delimited in '.', '-' or '/' 
-***************************
-*CTRAN(charlen, string, decimal, decvalue, outfield)
-*GETTOK(INFIELD, INLEN, TOKNUM, DELIM, OUTLEN, OUTFIELD)
-* ARGLEN(inlength, infield, outfield)
-********************************************
SET DEFCENT = 20
-*-SET &DBDATE = '10.1.09';
-SET &DBDATE = '10-1-09';
DEFINE FILE CAR
DBDATE1/A40V ='&DBDATE';
DBDATE2/A40V = CTRAN(LENV(DBDATE1,'I2'),DBDATE1, 45, 47, DBDATE2);
DBDATE/A40V = CTRAN(LENV(DBDATE2,'I2'),DBDATE2, 46, 47, DBDATE);
-*MM_Extract/A2 = GETTOK(DBDATE, 40, 1,'/', 40, MM_Extract);
MM_Extract/A2 = GETTOK(DBDATE, LENV(DBDATE,'I2'), 1,'/', 40, MM_Extract);
DD_Extract/A2 = GETTOK(DBDATE, LENV(DBDATE,'I2'), 2,'/', 40, DD_Extract);
YY_Extract/A4 = GETTOK(DBDATE, LENV(DBDATE,'I2'), 3,'/', 40, YY_Extract);
MM_LEN/I2 = ARGLEN(2, MM_Extract, MM_LEN) ;
DD_LEN/I2 = ARGLEN(2, DD_Extract, DD_LEN) ;
YY_LEN/I2 = ARGLEN(2, YY_Extract, YY_LEN) ;
MM_DBDATE/A2 = IF MM_LEN NE 2 THEN  EDIT(MM_Extract,'09') ELSE MM_Extract ;
DD_DBDATE/A2 = IF DD_LEN NE 2 THEN  EDIT(DD_Extract,'09') ELSE DD_Extract ;
YY_DBDATE/A4 = IF YY_LEN LT 2 THEN  EDIT(YY_Extract,'09') ELSE YY_Extract ;
ADBDATE/A8 =  MM_DBDATE || DD_DBDATE || YY_DBDATE ;
FDATE/MDYY = DATECVT(ADBDATE, 'A8MDYY', 'MDYY');
END
-RUN
TABLE FILE CAR
PRINT
DBDATE1
DBDATE2
DBDATE
MM_Extract
DD_Extract
YY_Extract
MM_LEN
DD_LEN
YY_LEN
MM_DBDATE
DD_DBDATE
YY_DBDATE
ADBDATE
FDATE
BY CAR NOPRINT
WHERE RECORDLIMIT EQ 1;
END
-EXIT
 

I defer to others to show a better way!

Thanks.

--changed the yy_extract datatype to A4 (to bring xxxx's).

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

personally I'd talk to the DBA,

What sort of person puts a date into a 40 character Varchar

Here's one that is a little shorter:

-**From ALPHA with variable length & format to DATE.
-**DB Format is assumed to be delimited in '.', '-' or '/'
-***************************
-*CTRAN(charlen, string, decimal, decvalue, outfield)
-*GETTOK(INFIELD, INLEN, TOKNUM, DELIM, OUTLEN, OUTFIELD)
-* ARGLEN(inlength, infield, outfield)
-********************************************
-* -SET &DBDATE = '10.1.09';
-SET &DBDATE = '10-1-09';
SET DEFCENT = 20
DEFINE FILE MACGYVER
DBDATE1/A40V ='&DBDATE';
DBDATE2/A40=DBDATE1;
DBDATE/A40=STRREP(40,DBDATE2,1,'.',1,'/',40,DBDATE);
DBDATE=STRREP(40,DBDATE,1,'-',1,'/',40,DBDATE);
-*MM_Extract/A2 = GETTOK(DBDATE, 40, 1,'/', 40, MM_Extract);
MM_EXTRACT/I2=EDIT(GETTOK(DBDATE,40,1,'/',2,'A2'));
DD_EXTRACT/I2=EDIT(GETTOK(DBDATE,40,2,'/',2,'A2'));
YY_EXTRACT/I2=EDIT(GETTOK(DBDATE,40,3,'/',2,'A2'));
IDATE/I6YMD=((YY_EXTRACT*100)+MM_EXTRACT)*100+DD_EXTRACT;
FDATE/MDYY=DATECVT(IDATE,'I6YMD','MDYY');
END
-RUN
TABLE FILE MACGYVER
PRINT COUNTER NOPRINT
DBDATE1
DBDATE2
DBDATE
MM_EXTRACT
DD_EXTRACT
YY_EXTRACT
IDATE
FDATE
WHERE COUNTER EQ 1;
END

I agree with JG but I have seen stranger.

Pretty Slick!.. Thanks jgelona.
I've made a small change to support YY's & with no delimiter in entry dates:

  
-**From ALPHA with variable length & format to DATE.
-**DB Format is assumed to be delimited in '.', '-', '/' or have no delimiter.
-*******************************************************************************
-*-SET &DBDATE = '10.1.2009';
-SET &DBDATE = '10012009';
SET DEFCENT = 20
DEFINE FILE MACGYVER
DBDATE1/A40V ='&DBDATE';
DBDATE2/A40=DBDATE1;
DBDATE/A40=STRREP(40,DBDATE2,1,'.',1,'/',40,DBDATE);
DBDATE=STRREP(40,DBDATE,1,'-',1,'/',40,DBDATE);
-*MM_Extract/A2 = GETTOK(DBDATE, 40, 1,'/', 40, MM_Extract);
MM_EXTRACT/I2=EDIT(GETTOK(DBDATE,40,1,'/',2,'A2'));
DD_EXTRACT/I2=EDIT(GETTOK(DBDATE,40,2,'/',2,'A2'));
YY_EXTRACT/I4=EDIT(GETTOK(DBDATE,40,3,'/',4,'A4'));
-*IDATE/I6YMD=((YY_EXTRACT)*100+MM_EXTRACT)*100+DD_EXTRACT;
IDATE/I8= IF (DD_EXTRACT EQ 0) THEN EDIT(DBDATE) ELSE (((YY_EXTRACT*100)+MM_EXTRACT)*100+DD_EXTRACT);
-*FDATE/MDYY = DATECVT(IDATE,'I6YMD','MDYY');
FDATE/MDYY= IF (DD_EXTRACT EQ 0) THEN DATECVT(IDATE,'I8MDYY','MDYY') ELSE DATECVT(IDATE,'I8YYMD','MDYY');
END
-RUN
TABLE FILE MACGYVER
PRINT COUNTER NOPRINT
DBDATE1
DBDATE2
DBDATE
MM_EXTRACT
DD_EXTRACT
YY_EXTRACT
IDATE
FDATE
WHERE COUNTER EQ 1;
END