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

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

how to calculate age

Post by shanthic2003 » Mon Sep 10, 2012 4:53 pm

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?

Many thanks for yyour reply.

edited by dbz to remove 'cool'

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Mon Sep 10, 2012 5:37 pm

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

Post by shanthic2003 » Mon Sep 10, 2012 6:34 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 ,
Thank you for the reply.
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

Post by William Collins » Mon Sep 10, 2012 8:03 pm

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.

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

Post by dbzTHEdinosauer » Mon Sep 10, 2012 8:23 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.

Post Reply

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