Solved

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

Posted on 2004-04-13
5,439 Views
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?

John
0
Question by:jtrapat1

LVL 4

Expert Comment

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

sixeyed earned 125 total points
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

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…