I need to create a field called AGE based on the Birthdate Field. Here is what I have so far.The way I have this now the define is calling the field AGE Dt and Printing a Date in the field that is 4 digit yr 2 digit mo and 2 digit day. I need the field to show the Age in Years based off of the Birthdate field. Suggestions? For example if my birthdate is 19640723 I want the Age field to show the age in years 43 or if less than 1 year I need it to show how many months or days old. How can I do this?

JOIN
OI_CHARGES.OI_CHARGES.MSR_NBR IN OI_CHARGES TO MULTIPLE MSR.MSR.MSR_NBR IN MSR
AS J0
END
JOIN
MSR.MSR.MSR_PAT_MPI_SET AND MSR.MSR.MSR_PAT_MPI_NBR IN OI_CHARGES TO MULTIPLE
MPI_PERSON.MPI_PERSON.MPI_SET AND MPI_PERSON.MPI_PERSON.MPI_NBR IN MPI_PERSON
AS J1
END
DEFINE FILE OI_CHARGES
CURRENTDT = TODAY(CURRENTDT);
AGE = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'Y');
END
TABLE FILE OI_CHARGES
PRINT
'OI_CHARGES.OI_CHARGES.RESP_GUAR_NBR'
'OI_CHARGES.OI_CHARGES.DT_OF_SERVICE'
'MPI_PERSON.MPI_PERSON.BIRTHDAY'
'MPI_PERSON.MPI_PERSON.ETHNIC_ORIGIN_CD'
'OI_CHARGES.OI_CHARGES.PAT_MPI_NBR'
'OI_CHARGES.OI_CHARGES.PAT_NBR'
'OI_CHARGES.OI_CHARGES.MSR_NBR'
'MSR.MSR.MSR_NBR'
'MPI_PERSON.MPI_PERSON.MPI_NBR'
'MPI_PERSON.MPI_PERSON.SSN'
AGE
HEADING
""
FOOTING
""
WHERE OI_CHARGES.OI_CHARGES.DT_OF_SERVICE GE 20070101;
WHERE OI_CHARGES.OI_CHARGES.APPL_SOURCE NOT LIKE 'EC';
ON TABLE NOTOTAL
END

First convert the birthdate to YYYYMM format and then divide by 12.

Here it is stepped out (BDATEI is formatted as I8.):

BDATE/I4YM = BDATEI/100;
NOWDATEI/I8 = &YYMD;
NOWDATE/I4YM = NOWDATEI/100;
AGEM/I4 = YM(BDATE, NOWDATE, 'I4');
AGE/I4 = AGEM/12;

Here is the short cut:

AGE/I4 = YM(BDATEI/100, &YYMD/100, 'I4')/12;

Try using following code in DEFINE Request..

CURRENTDT = TODAY(CURRENTDT);
AGE_Y = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'Y');
AGE_M = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'M');
AGE_D = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'D');
AGE = IF AGE_Y GE 1 THEN AGE_Y |' Years'
- ELSE IF AGE_M GE 1 THEN AGE_M|' Months'
- ELSE AGE_D|' Days';

I tried as follows and it is still not working. Can you show me using this code where it should be? I know I have to declare it like in sql and I am assuming define and declare are the same thing but I don't get how to do it in WebFocus. Can I use a SQL Statement in the middle of the WebFocus Statement and have it work or if I start in one language do I need to continue on in that language?

JOIN
OI_CHARGES.OI_CHARGES.MSR_NBR IN OI_CHARGES TO MULTIPLE MSR.MSR.MSR_NBR IN MSR
AS J0
END
JOIN
MSR.MSR.MSR_PAT_MPI_SET AND MSR.MSR.MSR_PAT_MPI_NBR IN OI_CHARGES TO MULTIPLE
MPI_PERSON.MPI_PERSON.MPI_SET AND MPI_PERSON.MPI_PERSON.MPI_NBR IN MPI_PERSON
AS J1
END

DEFINE MPI_PERSON.MPI_PERSON.BIRTHDAY

CURRENTDT = TODAY(CURRENTDT);
AGE_Y = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'Y');
AGE_M = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'M');
AGE_D = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'D');
AGE = IF AGE_Y GE 1 THEN AGE_Y |' Years'
- ELSE IF AGE_M GE 1 THEN AGE_M|' Months'
- ELSE AGE_D|' Days';
END

TABLE FILE OI_CHARGES
PRINT
'OI_CHARGES.OI_CHARGES.RESP_GUAR_NBR'
'OI_CHARGES.OI_CHARGES.DT_OF_SERVICE'
'MPI_PERSON.MPI_PERSON.BIRTHDAY'
'MPI_PERSON.MPI_PERSON.ETHNIC_ORIGIN_CD'
'OI_CHARGES.OI_CHARGES.PAT_MPI_NBR'
'OI_CHARGES.OI_CHARGES.PAT_NBR'
'OI_CHARGES.OI_CHARGES.MSR_NBR'
'MSR.MSR.MSR_NBR'
'MPI_PERSON.MPI_PERSON.MPI_NBR'
'MPI_PERSON.MPI_PERSON.SSN'
AGE

HEADING
""
FOOTING
""
WHERE OI_CHARGES.OI_CHARGES.DT_OF_SERVICE GE 20070101;
WHERE OI_CHARGES.OI_CHARGES.APPL_SOURCE NOT LIKE 'EC';
ON TABLE NOTOTAL
END

The DEFINE is done against the file, not the field:

DEFINE OI_CHARGES
CURRENTDT = TODAY(CURRENTDT);
AGE_Y = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'Y');
AGE_M = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'M');
AGE_D = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'D');
AGE = IF AGE_Y GE 1 THEN AGE_Y |' Years'
- ELSE IF AGE_M GE 1 THEN AGE_M|' Months'
- ELSE AGE_D|' Days';
END

TABLE FILE OI_CHARGES
........
  

I did not verify the code in the DEFINE but I know you copied it from an expert suggestion above.

What is the format of the date field you are manipulating?

Hi tried on my system againsit CAR file
Here is the code for it..

DEFINE FILE CAR
-*BDATE1 WITH COUNTRY/YYMD = '1970/03/15';
BDATE1 WITH COUNTRY/YYMD = '2007/03/15';
-*BDATE1 WITH COUNTRY/YYMD = '2007/12/02';
CDATE/YYMD = DATECVT(&YYMD,'I8YYMD','YYMD');
AGE_Y/D3 = DATEDIF(BDATE1,CDATE,'Y');
AGE_M/D3 = DATEDIF(BDATE1,CDATE,'M');
AGE_D/D3 = DATEDIF(BDATE1,CDATE,'D');
AGE/A30 = IF AGE_Y GE 1 THEN FTOA(AGE_Y, '(D3)', 'A3') |' Years'
ELSE IF AGE_M GE 1 THEN FTOA(AGE_M, '(D3)', 'A3') |' Months'
ELSE FTOA(AGE_D, '(D3)', 'A3') |' Days';
END
-RUN

TABLE FILE CAR
PRINT
CDATE
BDATE1
AGE
COUNTRY NOPRINT
HEADING
"WEBFOCUS REPORT"
WHERE READLIMIT EQ 10
WHERE RECORDLIMIT EQ 10
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLESHEET *
BORDER=1, BORDER-COLOR=SILVER,
FONT='ARIAL', SIZE=8, $
END

both the Birth day and Current should be in same Date Format.YYMD, if your Date is in different format use DATECVT function convert in to YYMD, and then use it DATEDIFF function,i guess it will work for you.

quote:

TODAY/YYMD = &YYMD;
BDATE/YYMD = AA010;
AGE/I3 = ((TODAY - BDATE)/365.25);

Our birthdate is stored in a DB2 date field we access as AA010. The above is what we use to calculate the age in a define, or you could put in as computes as well.

Note the BDATE could probably have been eliminated, this is really old code.

my understanding is that, he has to display only Years or Months or Days only as per the age not entire age..

Pl. correct me if i missed..

The code I would use is

DEFINE FILE XXX
TODAY/YYMD = '&DATEYYMD'
AGE_Y = DATEDIF(BIRTHDAY,TODAY,'Y');
AGE_M = DATEDIF(BIRTHDAY,TODAY,'M');
AGE_D = DATEDIF(BIRTHDAY,TODAY,'D');
AGE/A10 = IF AGE_Y GE 1 THEN EDIT(AGE_Y)|' Years'
- ELSE IF AGE_M GE 1 THEN EDIT(AGE_M)|' Months'
- ELSE EDIT(AGE_D)|' Days';
NEXTBIRTHDAY/YYMD=DATEADD(BIRTHDAY, 'Y', AGE_Y+1);
END

TABLE FILE XXX
PRINT 
   NAME
   BIRTHDAY
   AGE
   NEXTBIRTHDAY
BY SSN
END

This is based upon the suggestions from others, but there seems sometimes a mix of SET commands and DEFINE commands.
If you want to concatenate numbers and strings you can not just do it as above in a DEFINE, although this works in the "-" SET part of the reports.


I hope this will work for you

Happy Holidays.

For this example to work your date field must be formatted as "I8". If it is a different format, convert it first. (Change the date below and rerun for different dates.

-SET &BDATE = 19640723;
DEFINE FILE CAR
BDATE/I8 = &BDATE;
YEARS/I4 = YM(BDATE/100, &YYMD/100, 'I4')/12;
MONTHS/I9 = YM(BDATE/100, &YYMD/100, 'I9');
DAYS/I9 = YMD(BDATE, &YYMD);
AGEZEROS/A20 = IF YEARS GT 1 THEN EDIT(YEARS)||(' YEARS') ELSE
IF MONTHS GT 0 THEN EDIT(MONTHS)||(' MONTHS') ELSE EDIT(DAYS)||(' DAYS');
AGE/A20 = TRIM('L', AGEZEROS, 20, '0', 1, 'A20');
END-*
TABLE FILE CAR
PRINT BDATE
YEARS
MONTHS
DAYS
AGE
COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
END

Results:

PAGE 1

BDATE YEARS MONTHS DAYS AGE
19640723 43 521 15856 43 YEARS