Calculating a person's age

[WWRA]

Me again,,,the Discoverer newbie.

I'm trying to determine a person's age using the following code:
( TRUNC(SYSDATE) - TRUNC(Birthdate) )/365

Two problems with this formula:
1. Birthdate of 25-May-1951, the age displays as
53.60. I need to display only 53.

2. In some instances, the age displayed is not
correct. For example, if someone was born on
21-Dec-1951(based on today's date being
12/17/2004) the displayed age shows as 53.03
although it should be 52.

While I'm at it, I purchased the Oracle Discoverer Handbook. I'm finding that it is okay as far as generalties, but specific coding examples (for instance determining the age of someone) are virtually non-existent. Can someone recommend a good reference book I can purchase that gives more detailed coding examples?

TIA again for any assistance.
Discoverer Newbie

 

[sem]

I'm not quite familiar with Discoerer but your code is incorrect because it doesn't take into account leap-years. I don't think that another even better book may help you with such attitude.
BTW to get date difference in years you may use MONTHS_BETWEEN function.

Regards, Dima

http://seminihin.narod.ru

 

[WWRA]

Sem,
Thanks for the suggestion. Being new to Discoverer, I was unaware of a Months_Between function. I'll give it a try. Also, the leap years dilemma is something I'm seeking help to resolve. I suspect some of the Discoverer gurus on this forum have already figured a way to accurately determine someone's age. Thus, the question I asked.

I'm sorry if I offended you in some way, but I don't think the question I posed infers that I have an "attitude". I was merely seeking additional reference materials that are more detailed and offered specific coding examples rather than the general "How To" book that I now have. Some of the gurus that frequent this site can probably suggest some excellent reference materials as well as assist with my question. One day I hope to be the one answering questions instead of seeking answers. Until then, I'll continue tapping the resources of this excellent forum.

Sincerely,
New to Discoverer

 

[sem]

This function doesn't belong to Discoverer area at all, but using that tool assumes at least basic sql knowledge. That was the origin of my "aggressiveness".

Regards, Dima

http://seminihin.narod.ru

 

[hilfy]

Or you can try something like this:

Floor(( TRUNC(SYSDATE) - TRUNC(Birthdate) )/365.25)

This should give you the integer-part of the calculation which is the age.

-Dell

 

[WWRA]

hilfy,

Thx for the suggestion. The whole number I'm getting now is correct; however I'm still getting a decimal.
For example, birthdate 21-DEC-1951 returns age 52.99, based on SYSDATE of 17-DEC-2004.
Much better than I had, at least the 52 is correct.

According to my Discoverer Handbook, FLOOR should be dropping the .99. I'll keep working with it and try to get it to display only the whole number.

If you have any more suggestions, please let me know.

Thanks again for taking the time to help me.

 

[sem]

You may get the a whole number in many ways but you haven't explained what you need exactly. Depending on your requirements FLOOR, ROUND or TRUNC may suit.

Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

To expand on Dimas excellent suggestions, try

[tt]SELECT Floor(Months_Between(SYSDATE,<your_date>)/12)
FROM dual[/tt]

 

[WWRA]

Thanks for everyone's help in solving my dilemma with calculating a person's age.

Building on everyone's suggestions, I finally solved my problem with the following code:
TO_CHAR(FLOOR(MONTHS_BETWEEN(SYSDATE,Birthdate)/12))
I now get the correct age without a decimal point.

As a newbie to Discoverer, I really appreciate all of your help. It's comforting to know that when reading the manuals and reference books yield no results, there are some Discoverer and SQL gurus I can get some insight from.

THANKS AGAIN!!