Solved

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

Posted on 2004-04-13
2
6,079 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

830 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