## how to calculate age

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

shanthic2003
Member
Posts: 5
Joined: Sat Sep 08, 2012 12:17 pm

### how to calculate age

Hi,

I have a DB2 table where in birth date is stored as birthdate char-08 in 'YYYYMMDD' format.

Now I have to calculate if he has completed 18 years.

How can I achive this?

edited by dbz to remove 'cool'

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am
Subtract 18 from YYYY. If new complete value is less than or equal to Brithdate, then the person is 18.

It works because the date fields are in that order. Mess them around and it won't work in one shot.

shanthic2003
Member
Posts: 5
Joined: Sat Sep 08, 2012 12:17 pm
William Collins wrote:Subtract 18 from YYYY. If new complete value is less than or equal to Brithdate, then the person is 18.

It works because the date fields are in that order. Mess them around and it won't work in one shot.

Hi William ,
Subtracting the year works in most cases but it does not works in below case where

Current date = 20120910
Birth date = 19941001

diff in year = +18
diff in month = -1
diff in days = +9

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am
Well, I've clearly written it the wrong way around. Otherwise someone born yesterday would be 18.

If Birthdate is less than or equal to calculated date (YYYY-18)MMDD then the person is 18 or older.

dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm
William,

one of the problems is that char ( 8 ) ends up looking like char(cool

i corrected his initial post,
but since the TS has a date stored in a char field,
the sql is a little more complex.
the birthdate column must be cast to a date type first, before the subtraction.

so the answer for our sql challenged poster would be something along the lines of

Code: Select all

``````SELECT person
FROM table
WHERE &#40;birthdate in char format cast as a date&#41; < current_date - 18 years``````
or

Code: Select all

``WHERE birthdate < &#40; &#40;current_date - 18 years&#41; cast as char&#40;8&#41; &#41;``
or

Code: Select all

``WHERE &#40;birthdate in char format cast as a date&#41; + 18 years < current_date``
to cast birthdate as date, you need to
date(substr(birthdate,1,4) || char('-',1) || substr(birthdate,5,2) || char('-',1) || substr(birthdate,7,2))

conversely
substr(char(current_date - 18 years,ISO),1,4) || substr(char(current_date,ISO),6,2) || substr(char(current_date,ISO),9,2)

if you are not using db2 v9, you need to check your sql reference for
the proper CHAR cast function parameters.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

# FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
• JCL Tutorial
Covers all important JCL concepts.
• Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3.
• DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming.
• SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples
• CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions

Other References
Mainframe Tools and others