Solved

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

Posted on 2004-04-13
2
5,439 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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 (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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now