Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Birthdate Conversion for Age in years months or days

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Birthdate Conversion for Age in years months or days
 Login/Join
 
Silver Member
posted
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


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Guru
posted Hide Post
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;


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Expert
posted Hide Post
DEFINE FILE CAR
BDATE WITH COUNTRY/YYMD = '1970/03/15';
TDATE/YYMD = '&DATEYYMD';
END

TABLE FILE CAR
PRINT
BDATE
COMPUTE AGE_Y/I8 = DATEDIF(BDATE, TDATE, 'Y');
COMPUTE AGE_M/I8 = DATEDIF(BDATE, TDATE, 'M');
COMPUTE AGE_D/I8 = DATEDIF(BDATE, TDATE, 'D');

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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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';




Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF
 
Posts: 52 | Location: NJ,USA | Registered: May 26, 2004Report This Post
Silver Member
posted Hide Post
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


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
What is the format of the date field you are manipulating?


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Gold member
posted Hide Post
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.




Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF
 
Posts: 52 | Location: NJ,USA | Registered: May 26, 2004Report This Post
Virtuoso
posted Hide Post
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.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Gold member
posted Hide Post
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..




Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF
 
Posts: 52 | Location: NJ,USA | Registered: May 26, 2004Report This Post
Virtuoso
posted Hide Post
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Guru
posted Hide Post
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


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Birthdate Conversion for Age in years months or days

Copyright © 1996-2020 Information Builders