Hi,

I have a date that is defined in A8 format. I want to convert it to A8YYMD format to insert into DB2 date field. The following transform gives me a blank date when the OLD_DATE is invalid(i.e 20030431, 20040931 etc). I hav not control over the data.

NEW_DATE/A8YYMD = OLD_DATE

OLD_DATE is defined as A8. How do I move the date to end of month and convert it into A8YYMD format in one step.

Thanks.

Here is an "old school" way to do it. There are newer functions in FOCUS/WebFOCUS that probably do what would like to do in fewer steps but I think this will get the results you are looking for.

TABLE FILE CAR
PRINT RCOST DCOST
COMPUTE
ORG_DATE/A8='20050231';
FST_DATE/I6YYM=EDIT(EDIT(ORG_DATE,'999999')) + 1;
SEC_DATE/YYMD=FST_DATE;
NEW_DATE/YYMD=SEC_DATE-1;
BY COUNTRY
END

Hi. You can also try this:

TABLE FILE CAR
PRINT COUNTRY
COMPUTE
DTE_A/A8 = '20050526'; NOPRINT
COMPUTE
DTE_NEW/A8YYMD = DATECVT(DATEMOV(DATECVT(DTE_A, 'A8YYMD', 'YYMD'),
'EOM'), 'YYMD', 'A8YYMD');
WHERE RECORDLIMIT EQ 1
END

Hi,

All of your suggestions work. But I want to move the date to end of month only it is an invalid it. How do I check for that.

NEW_DATE/A8YYMD = OLD_DATE

I tried NEW_DATE = ' '
but it did not work.

Hi,

All of your suggestions work. But I want to move the date to end of month only if is an invalid date, like 20040931. How do I check for that.

NEW_DATE/A8YYMD = OLD_DATE

I tried NEW_DATE = ' '
but it did not work.

Try this:
-*COMPUTE DTEA/A8 = '20050526'; NOPRINT
COMPUTE DTEA/A8 = '20050532'; NOPRINT
COMPUTE DTEB/A8 = EDIT(DTEA,'999999') | '01';
COMPUTE DTE_NEW/A8YYMD= IF (DATECVT(DTEA, 'A8YYMD', 'YYMD') EQ 0) THEN
DATECVT(DATEMOV(DATECVT(DTEB, 'A8YYMD', 'YYMD'),'EOM'),'YYMD', 'A8YYMD')
ELSE DTEA;
Couple things to note:
1) DATECVT returns zero when the dated tested is invalid so you can then test for the zero
2) the DTEB field is necessary because DATEMOV wants a valid date for input. So, you set the date to the first of the month then move it to the end via DATEMOV.

Hope this helps.