Age In Years, Months and Days

arif677 · New User Joined: 22 Jun 2005 Posts: 11

Hi,

I was trying to calculate the age of a person in years, months and days as on present day from DB2.

The DB2 table has the column for the Date of Birth of a person in the format yyyymmdd.

I need to write an SQL query and need to find out the age in Years, Months and Days.

Any help greatly appreciate.

Thanks in advance.

Arif.

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

Hi,

I think DB2 doesnot have any built in function to give you the AGE.
Following approach will work but bit tedious process. I have added only two scenarios. You have to list all the possible scenarios and add them as different WHEN conditions to the CASE statement.
SELECT
CASE
-- DOB AND TODAYS DAY & MONTHS ARE SAME
WHEN MONTH(DATE('1979-12-17')) - MONTH(CURRENT DATE) = 0
AND
(DAY(DATE('1979-12-17')) - DAY(CURRENT DATE)) = 0 THEN
CHAR(YEAR(CURRENT DATE) - YEAR(DATE ('1979-12-17')))
||'YEARS'
-- DOB AND TODAYS MONTHS ARE SAME BUT DATES ARE DIFFERENT
WHEN MONTH(DATE('1979-12-17')) - MONTH(CURRENT DATE) = 0
AND
(DAY(DATE('1979-12-17')) - DAY(CURRENT DATE)) > 0 THEN
CHAR(YEAR(CURRENT DATE) -
YEAR(DATE ('1979-12-17')) - 1) ||
'YEARS' || '11 MONTHS' ||
CHAR(DAY((LAST_DAY(CURRENT DATE - 1 MONTH))) -
DAY(DATE('1979-12-17'))
DAY(CURRENT DATE))
|| 'DAYS'
END
FROM SYSIBM.SYSDUMMY1;

I am just giving this as a possible solution for your problem.

Thanks & Regards,
Mallik

Craq Giegerich · Posted: Tue Dec 09, 2008 6:27 pm

That's a bunch of bs.

Look in the DB2 SQL Reference for date arithmatic.

current date - Birthdate

arif677 · New User Joined: 22 Jun 2005 Posts: 11

Hi Mallik & Crag,

I found the DB2 function TIMESTAMPDIFF. I will write an sql query.

Thank you so much.

Arif.

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

Hi Arif,

Would be appretiated if you post the SQL.

Thanks & Regards,
Mallik

arif677 · New User Joined: 22 Jun 2005 Posts: 11

Hi Mallik,

I will provide the SQL soon... I am working on the other high-priority request... I definetly post the SQL...... Thanks.

Arif.

Suresh Ponnusamy · Posted: Thu Dec 11, 2008 8:16 pm

Hi

Please try this one

Suresh Ponnusamy · Posted: Thu Dec 11, 2008 9:19 pm

Sorry, I have not given exact answer for your query.

To get Age in Years,

dick scherrer · Posted: Thu Dec 11, 2008 11:35 pm

Hello,

I believe the requirement is to show all 3 as the result.

For example "Jim" is 28 years, 5 months, and 4 days old. . .

Possibly, i misunderstand. . .

Suresh Ponnusamy · Posted: Fri Dec 12, 2008 12:32 am

Hi

Please try the below one.

Suresh Ponnusamy · Posted: Fri Dec 12, 2008 12:56 am

Oops.. Sorry a Small change

Craq Giegerich · Posted: Fri Dec 12, 2008 1:19 am

Is the date of birth defined as date column?

If not it's a poor database design.

arif677 · New User Joined: 22 Jun 2005 Posts: 11

Hello,

Yes. The Date of Birth is defined as timestamp. The query provided by Suresh is workig fine when I run the query in the online web SQL executor. But our shop doesn't recongnize the DB2 function TIMESTAMPDIFF. I am getting compilation errors when I am writing the sql with the function TIMESTAMPDIFF. (The DB2 precompiler doesn't recognize the function). Hence I am using
YEAR(CURRENT TIMESTAMP - TIMESTAMP(DOB)) to get the years part of the age and manually calculating the months difference in the COBOL program.

Please let me know if you have any idea why the DB2 precompiler not recognizing the DB2 functions.

Thanks alot for all your help!

Arif.

Craq Giegerich · Posted: Tue Dec 16, 2008 10:04 pm

current date - date(date_Of_birth) will return a decimal(8,0) results, the leftmost 4 digits are the years, then 2 digits for the months, and the righmost two are the days difference.

Why bother with the timestampdiff function, just look in the sql reference manual under date arithmetic as I suggested earlier.

Anil.koyilada · New User Joined: 02 May 2013 Posts: 1 Location: INDIA

while calculating age, i have the below scenario that pull a value less by one.

when DOB is '2014-01-31' and current date is 2015-02-28, the age in months should pull 13 but instead i'm getting only 12. below are the sqls:

--------+---------+---------+---------+---------+-------
SELECT YEAR(DATE(('2015-02-28')) -
DATE('2014-01-31'))
FROM SYSIBM.SYSDUMMY1;
--------+---------+---------+---------+---------+-------

--------+---------+---------+---------+---------+-------
1
SNE610I NUMBER OF ROWS DISPLAYED IS 1
SNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 1
--------+---------+---------+---------+---------+-------
SELECT MONTH(DATE(('2015-02-28')) -
DATE('2014-01-31'))
FROM SYSIBM.SYSDUMMY1;
--------+---------+---------+---------+---------+-------

--------+---------+---------+---------+---------+-------
0

Basically this happens only when DOB is month end and calculated date is also another month.. In Oracle SQL, the value is coming good.. Please help. Thanks in advance

enrico-sorichetti · Posted: Wed Jun 17, 2015 10:38 pm

any reason to resurrect - unless forced by some kind of &religion

- a 7 years old topic.

even if the subject is the same good manners dictate to start a new topic!