how to calculate age

[shanthic2003]

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]

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]

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]

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]

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.