Solved

Calculation of Age From db2 Table in format MM/DD/YYYY

Posted on 2004-04-13
2
5,904 Views
Last Modified: 2008-01-16
I need help in calculating an age from a db2 table.
The age from the table is in the format: MM/DD/YYYY.

Are there any db2 functions that could help me calculate this number?
I would need to get the current date and subtract the field in the database; but would I have to parse out the mm,dd, and yyyy fields?

For example, in testing a birth_date of 11/15/1809,
I tried db2 "select current date - date(birth_date) from tablename where birth_date = '11/15/1809'"
but this returns the date in a format of 1940428.
which is the number of years followed by the number of days, I think.

Is there an easier way to perform this calculation?

Thanks In Advance.
John  
0
Comment
Question by:jtrapat1
2 Comments
 
LVL 4

Expert Comment

by:bondtrader
ID: 10815004
There are many ways to do this, but here is a simple way:

select  integer( floor( (current date - birth_date ) / 10000 )) from tablename


Hope this helps

0
 

Accepted Solution

by:
sixeyed earned 125 total points
ID: 10815153
Hi John,

you don't need to parse the dates, but you need to convert them to numbers of days before subtracting. E.g.:

select days(current_date) - days(date('12/31/1935'))...

- will give you the number of days in between.

If you want to convert the result to months/years etc, specify the precision you want in the operand:

select (days(current_date) - days(date('01/29/1978'))) / 365.00...

- will give you the number of years to 2 d.p.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question