Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
arif677

New User

Joined: 22 Jun 2005
Posts: 11

 Posted: Tue Dec 09, 2008 6:35 am    Post subject: Age In Years, Months and Days 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    Post subject: 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    Post subject: 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    Post subject: Reply to: Age In Years, Months and Days 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    Post subject: 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    Post subject: Reply to: Age In Years, Months and Days 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    Post subject:

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    Post subject:

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: 19254
Location: Inside the Matrix

 Posted: Thu Dec 11, 2008 11:35 pm    Post subject: 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    Post subject:

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    Post subject:

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    Post subject: 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    Post subject: Reply to: Age In Years, Months and Days 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    Post subject: 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    Post subject: 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 Member

Joined: 14 Mar 2007
Posts: 10701
Location: italy

 Posted: Wed Jun 17, 2015 10:38 pm    Post subject: Reply to: Age In Years, Months and Days 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!
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How to remove remote file older than ... ssuthagar All Other Mainframe Topics 4 Thu Jun 04, 2020 6:26 am Mainframe - PL1, JCL, DB2 opening - 5... Rathish123 Mainframe Jobs 0 Tue Jul 16, 2019 8:13 pm Mainframe + Card domain exp. req. 5-6... capricorn Mainframe Jobs 0 Wed Oct 24, 2018 5:10 pm 3-6 years - Mainframe Opennings in HS... Saikat Sengupta Mainframe Jobs 0 Mon Oct 22, 2018 2:57 pm Add 5 days to a date Ron Klop DB2 19 Wed Jul 25, 2018 8:11 pm

 © 2003-2020 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us