IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Age In Years, Months and Days


IBM Mainframe Forums -> DB2
Post new topic   This topic is locked: you cannot edit posts or make replies.
View previous topic :: View next topic  
Author Message
arif677

New User


Joined: 22 Jun 2005
Posts: 11

PostPosted: Tue Dec 09, 2008 6:35 am
Reply with quote

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.
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Tue Dec 09, 2008 12:36 pm
Reply with quote

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
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Tue Dec 09, 2008 6:27 pm
Reply with quote

That's a bunch of bs.

Look in the DB2 SQL Reference for date arithmatic.

current date - Birthdate
Back to top
View user's profile Send private message
arif677

New User


Joined: 22 Jun 2005
Posts: 11

PostPosted: Wed Dec 10, 2008 4:56 am
Reply with quote

Hi Mallik & Crag,

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

Thank you so much.

Arif.
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Wed Dec 10, 2008 9:53 am
Reply with quote

Hi Arif,

Would be appretiated if you post the SQL.

Thanks & Regards,
Mallik
Back to top
View user's profile Send private message
arif677

New User


Joined: 22 Jun 2005
Posts: 11

PostPosted: Thu Dec 11, 2008 7:37 am
Reply with quote

Hi Mallik,

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

Arif.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


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

PostPosted: Thu Dec 11, 2008 8:16 pm
Reply with quote

Hi

Please try this one

Code:

SELECT TIMESTAMPDIFF(256,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -   TIMESTAMP('1982-10-29-00.00.00.000000')))                           
 FROM SYSIBM.SYSDUMMY1;;;                                             
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


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

PostPosted: Thu Dec 11, 2008 9:19 pm
Reply with quote

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

To get Age in Years,

Code:

SELECT TIMESTAMPDIFF(256,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -TIMESTAMP(<TABLE COLUMN>,CURRENT TIME)))               
 FROM SYSIBM.SYSDUMMY1;;;                               


To get Age in Months

Code:

SELECT TIMESTAMPDIFF(64,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -TIMESTAMP(<TABLE COLUMN>,CURRENT TIME)))               
 FROM SYSIBM.SYSDUMMY1;;;                               


To get Age in Days

Code:

SELECT TIMESTAMPDIFF(16,CHAR(TIMESTAMP(CURRENT TIMESTAMP) -TIMESTAMP(<TABLE COLUMN>,CURRENT TIME)))               
 FROM SYSIBM.SYSDUMMY1;;;                               


Hope this helps.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Dec 11, 2008 11:35 pm
Reply with quote

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. . .
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


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

PostPosted: Fri Dec 12, 2008 12:32 am
Reply with quote

Hi

Please try the below one.

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;;                                               
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


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

PostPosted: Fri Dec 12, 2008 12:56 am
Reply with quote

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;;                                               
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Fri Dec 12, 2008 1:19 am
Reply with quote

Is the date of birth defined as date column?

If not it's a poor database design.
Back to top
View user's profile Send private message
arif677

New User


Joined: 22 Jun 2005
Posts: 11

PostPosted: Tue Dec 16, 2008 9:41 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Tue Dec 16, 2008 10:04 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Anil.koyilada

New User


Joined: 02 May 2013
Posts: 1
Location: INDIA

PostPosted: Wed Jun 17, 2015 6:26 pm
Reply with quote

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
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Jun 17, 2015 10:38 pm
Reply with quote

any reason to resurrect - unless forced by some kind of &religion icon_wink.gif - a 7 years old topic.

even if the subject is the same good manners dictate to start a new topic!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need to fetch data from so many DB2 t... DB2 9
No new posts Amount of days between two dates PL/I & Assembler 8
No new posts Mainframe developers 3 - 6 years exp Mainframe Jobs 0
No new posts How to remove remote file older than ... All Other Mainframe Topics 4
No new posts Mainframe - PL1, JCL, DB2 opening - 5... Mainframe Jobs 0
Search our Forums:

Back to Top