# Age In Years, Months and Days

Author Message
arif677

New User

Joined: 22 Jun 2005
Posts: 11

 Posted: Tue Dec 09, 2008 6:35 am 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

 Posted: Tue Dec 09, 2008 12:36 pm 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

Senior Member

Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

 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

 Posted: Wed Dec 10, 2008 4:56 am 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

 Posted: Wed Dec 10, 2008 9:53 am Hi Arif, Would be appretiated if you post the SQL. Thanks & Regards, Mallik
arif677

New User

Joined: 22 Jun 2005
Posts: 11

 Posted: Thu Dec 11, 2008 7:37 am 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

Active User

Joined: 22 Feb 2008
Posts: 107
Location: New York

Posted: Thu Dec 11, 2008 8:16 pm

Hi

 Code: SELECT TIMESTAMPDIFF(256,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -   TIMESTAMP('1982-10-29-00.00.00.000000')))                             FROM SYSIBM.SYSDUMMY1;;;
Suresh Ponnusamy

Active User

Joined: 22 Feb 2008
Posts: 107
Location: New York

Posted: Thu Dec 11, 2008 9:19 pm

To get Age in Years,

 Code: SELECT TIMESTAMPDIFF(256,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -TIMESTAMP(,CURRENT TIME)))                 FROM SYSIBM.SYSDUMMY1;;;

To get Age in Months

 Code: SELECT TIMESTAMPDIFF(64,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -TIMESTAMP(,CURRENT TIME)))                 FROM SYSIBM.SYSDUMMY1;;;

To get Age in Days

 Code: SELECT TIMESTAMPDIFF(16,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -TIMESTAMP(,CURRENT TIME)))                 FROM SYSIBM.SYSDUMMY1;;;

Hope this helps.
dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19245
Location: Inside the Matrix

 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

Active User

Joined: 22 Feb 2008
Posts: 107
Location: New York

Posted: Fri Dec 12, 2008 12:32 am

Hi

 Code: SELECT                                                                  (SELECT TIMESTAMPDIFF(256,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -               TIMESTAMP('1982-10-29-00.00.00.000000'))) AS YEAR                       FROM SYSIBM.SYSDUMMY1),                                              (SELECT MOD(TIMESTAMPDIFF(64,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -           TIMESTAMP('1982-10-29-00.00.00.000000'))),12) AS MONTH                   FROM SYSIBM.SYSDUMMY1),                                              (SELECT MOD(TIMESTAMPDIFF(16,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -           TIMESTAMP('1982-10-29-00.00.00.000000'))),365) AS DAYS                   FROM SYSIBM.SYSDUMMY1)                                                 FROM SYSIBM.SYSDUMMY1;;
Suresh Ponnusamy

Active User

Joined: 22 Feb 2008
Posts: 107
Location: New York

Posted: Fri Dec 12, 2008 12:56 am

Oops.. Sorry a Small change

 Code: SELECT                                                                  (SELECT TIMESTAMPDIFF(256,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -               TIMESTAMP('1982-10-29-00.00.00.000000'))) AS YEAR                       FROM SYSIBM.SYSDUMMY1),                                              (SELECT MOD(TIMESTAMPDIFF(64,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -           TIMESTAMP('1982-10-29-00.00.00.000000'))),12) AS MONTH                   FROM SYSIBM.SYSDUMMY1),                                              ((SELECT MOD(TIMESTAMPDIFF(16,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -           TIMESTAMP('1982-10-29-00.00.00.000000'))),365) AS DAYS                   FROM SYSIBM.SYSDUMMY1) -                                              ((SELECT MOD(TIMESTAMPDIFF(64,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -           TIMESTAMP('1982-10-29-00.00.00.000000'))),12) AS MONTH                   FROM SYSIBM.SYSDUMMY1) * 30))                                         FROM SYSIBM.SYSDUMMY1;;
Craq Giegerich

Senior Member

Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

 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

 Posted: Tue Dec 16, 2008 9:41 pm 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

Senior Member

Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

 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.

New User

Joined: 02 May 2013
Posts: 1
Location: INDIA

 Posted: Wed Jun 17, 2015 6:26 pm 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

Senior Moderator

Joined: 14 Mar 2007
Posts: 10714
Location: italy

 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!
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics How to remove remote file older than ... All Other Mainframe Topics 4 Mainframe - PL1, JCL, DB2 opening - 5... Mainframe Jobs 0 Mainframe + Card domain exp. req. 5-6... Mainframe Jobs 0 3-6 years - Mainframe Opennings in HS... Mainframe Jobs 0 Add 5 days to a date DB2 19
Search our Forums: