Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Age In Years, Months and Days

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Age In Years, Months and Days
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Age In Years, Months and Days
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    Post subject:
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    Post subject: Reply to: Age In Years, Months and Days
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    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Thu Dec 11, 2008 11:35 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Age In Years, Months and Days
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    Post subject:
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

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

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to find jobs that are in schedule... anilkumar922 All Other Mainframe Topics 5 Tue Jun 28, 2016 10:01 am
No new posts Mainframe Openings for 3 to 5 years i... muralikrishnan_new Mainframe Jobs 0 Fri Jun 17, 2016 4:48 pm
This topic is locked: you cannot edit posts or make replies. BAE Systems - Recruitment Open Days -... Express Mainframe Jobs 0 Fri Apr 22, 2016 8:19 pm
No new posts Write a job to delete the files aging... Abhinav Roy JCL & VSAM 4 Fri Dec 18, 2015 7:17 pm
No new posts Need a query to find 30 days old data mf_karthik DB2 8 Fri Oct 23, 2015 5:24 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us