Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate exact age (Oracle)
How to calculate exact age [message #527976] Fri, 21 October 2011 02:20 Go to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days

Regards

Faheem

Re: How to calculate exact age [message #527979 is a reply to message #527976] Fri, 21 October 2011 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This has been answered so many times that I'm suprised you didn't find it when you searched before posting.

Regards
Michel

[Updated on: Fri, 21 October 2011 02:32]

Report message to a moderator

Re: How to calculate exact age [message #528122 is a reply to message #527979] Fri, 21 October 2011 15:19 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Give you a hint. Read up on the months_between function and some simple math.
Re: How to calculate exact age [message #528213 is a reply to message #528122] Sun, 23 October 2011 03:07 Go to previous messageGo to next message
ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,
Try it.
Select trunc(months_between(sysdate,dob)/12) year,
       trunc(mod(months_between(sysdate,dob),12)) month,
       trunc(Sysdate-Add_Months(DOB,Trunc(months_between(sysdate,dob)/12)*12
       +TRUNC(MOD(months_between(sysdate,dob),12)))) DAY
       From (Select to_date('01041977','DDMMYYYY') dob From Dual);

As a Result.
YEAR                   MONTH                  DAY                    
---------------------- ---------------------- ---------------------
34                     6                      22                     


Regards,
Irfan

[Updated on: Sun, 23 October 2011 03:28] by Moderator

Report message to a moderator

Re: How to calculate exact age [message #528215 is a reply to message #528213] Sun, 23 October 2011 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You are a new comer, you know you have to let OP does his homework and at least shows us what he tried.

Regards
Michel
Re: How to calculate exact age [message #528217 is a reply to message #528215] Sun, 23 October 2011 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For information, I posted a query far ago in How to get the difference between 2 dates in format years/months/days.

Regards
Michel
Re: How to calculate exact age [message #529013 is a reply to message #528217] Fri, 28 October 2011 03:25 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
that works very well if i know date of birth but my problem is how to calculate age if any body tell me that his age
is 27.50 years than how to calculate date of birth

Select trunc(months_between(sysdate,dob)/12) year,
trunc(mod(months_between(sysdate,dob),12)) month,
trunc(Sysdate-Add_Months(DOB,Trunc(months_between(sysdate,dob)/12)*12
+TRUNC(MOD(months_between(sysdate,dob),12)))) DAY
From (Select to_date('01041977','DDMMYYYY') dob From Dual);
Re: How to calculate exact age [message #529014 is a reply to message #529013] Fri, 28 October 2011 03:44 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Convert 27.5 to months and use the add_months function.
Re: How to calculate exact age [message #529015 is a reply to message #529013] Fri, 28 October 2011 03:51 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
qanita786 wrote on Fri, 28 October 2011 10:25
that works very well if i know date of birth but my problem is how to calculate age if any body tell me that his age
is 27.50 years than how to calculate date of birth

Will he really tell you it with precision of 1 day (that is 0.00273224 or 0.002739726 year in 366/365 days' year)? Or even more precisely (hour/minute)?
Maybe you should firstly exactly define, what that fraction means and then convert it to that unit(s) (days/days+months/whatever).
Re: How to calculate exact age [message #529018 is a reply to message #529013] Fri, 28 October 2011 04:00 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
that works very well if i know date of birth but my problem is how to calculate age if any body tell me that his age
is 27.50 years than how to calculate date of birth

Then why did you say in your first post:
Quote:
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days

i.e. the opposite of what you are now saying that your problem is.
I have a feeling that you are still not giving us the full details of your actual requirements. Can you please be more specific as to what you actually need.
how to get birth date if age is 27 years 6 months & 22 days [message #529112 is a reply to message #527976] Fri, 28 October 2011 13:57 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
how to get birth date if age is 27 years 6 months & 22 days

Regards

Faheem
Re: How to calculate exact age [message #529113 is a reply to message #529018] Fri, 28 October 2011 14:00 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
i want how to get birth date if age is 27 years 6 months & 22 days
Re: How to calculate exact age [message #529114 is a reply to message #529113] Fri, 28 October 2011 14:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ADD_MONTHS(SYSDATE-22, -((27*12)+6))
Re: How to calculate exact age [message #529115 is a reply to message #529113] Fri, 28 October 2011 14:05 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
i want how to get birth date if age is 27 years 6 months & 22 days or age is 26.50
Re: How to calculate exact age [message #529116 is a reply to message #529115] Fri, 28 October 2011 14:12 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
if age is 26.50 than how to calculate date of birth
Re: How to calculate exact age [message #529117 is a reply to message #529116] Fri, 28 October 2011 14:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>if age is 26.50 than how to calculate date of birth
I decline to continue to play your silly games.
You're On Your Own!
Re: How to calculate exact age [message #529119 is a reply to message #529117] Fri, 28 October 2011 14:22 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
dear this is demand by a hospital when we asked age from patient they tell us 26.50 years and we have not enough time to calculate date of birth dear swan its not silly its public demand so try to solve issue
Re: How to calculate exact age [message #529121 is a reply to message #529119] Fri, 28 October 2011 14:25 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member

1* select add_months(trunc(sysdate), -1 * ((26*12) + 6)) from dual
>/

ADD_MONTH
---------
28-APR-85
Re: How to calculate exact age [message #529122 is a reply to message #529119] Fri, 28 October 2011 14:25 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
It would be far more sensible to ask the person's date of birth then rather than their age. If the don't know their date of birth then how on earth can you expect them to know their eact age? You claim makes no sense and apart from that, you have been given the tools that you require to answer the problem, all you need to do is use your head a little and actually think about the problem
Re: How to calculate exact age [message #529124 is a reply to message #529119] Fri, 28 October 2011 14:27 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
And on a similar vein, yes it is silly to expect to be able to accurately calculate someone's date of birth from what will almost certainly be a very rough estimate of their age.
Re: How to calculate exact age [message #529125 is a reply to message #529122] Fri, 28 October 2011 14:27 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
While I got tired and answered it, this has to be homework.
Re: How to calculate exact age [message #529126 is a reply to message #529125] Fri, 28 October 2011 14:28 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Bill B wrote on Fri, 28 October 2011 20:27
While I got tired and answered it, this has to be homework.

Agree 100%
Re: How to calculate exact age [message #529128 is a reply to message #529125] Fri, 28 October 2011 14:33 Go to previous messageGo to next message
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Besides, it depends on who you ask. Small children have no idea. School children know it in a day ("I'll be 13 next Tuesday"), older people (let's suppose at the age of 68) will say "bah, doesn't matter ... I'm 70". Women? A special case. They won't admit a day more than 32.
Re: how to get birth date if age is 27 years 6 months & 22 days [message #529132 is a reply to message #529112] Fri, 28 October 2011 14:48 Go to previous messageGo to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

I think this can help:

http://www.java2s.com/Code/Oracle/Numeric-Math-Functions/Usefloormodanddatecalculationtoshowhowmanyweeksanddaysanemployeehasbeenworki nghere.htm
Re: How to calculate exact age [message #529134 is a reply to message #529119] Fri, 28 October 2011 15:03 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
qanita786 wrote on Fri, 28 October 2011 15:22
dear this is demand by a hospital when we asked age from patient they tell us 26.50 years and we have not enough time to calculate date of birth dear swan its not silly its public demand so try to solve issue


And you expect a patient to calculate their age for you to be bale to use accurately in an equation?

Do you expect someone who is 35 years and 42 days old to say "I am 35.115068493 years old" or for someone to even know how many days past their birthday they are?

This is ridiculous, not silly.

[Updated on: Fri, 28 October 2011 15:05]

Report message to a moderator

Re: how to get birth date if age is 27 years 6 months & 22 days [message #529135 is a reply to message #529132] Fri, 28 October 2011 15:08 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
itralarcon wrote on Fri, 28 October 2011 15:48
I think this can help:

http://www.java2s.com/Code/Oracle/Numeric-Math-Functions/Usefloormodanddatecalculationtoshowhowmanyweeksanddaysanemployeehasbeenworki nghere.htm


I don't see how this helps as this uses a DATE as we have been saying and not the ludicrous requirement of a person's age.
Re: how to get birth date if age is 27 years 6 months & 22 days [message #529390 is a reply to message #529135] Mon, 31 October 2011 09:13 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Hello, I'm new for PL/SQL but I want to share MS SQL and I think it's not so hard to modify this correct to work in PL/SQL.

My code:

DECLARE @birthdate datetime
DECLARE @xdate datetime 
DECLARE @years int 

SELECT @birthdate = CONVERT(datetime, '30.10.1985', 104) 
SELECT @xdate = CONVERT(datetime, '21.07.2011', 104) 
SELECT @years = DATEDIFF(yy, @birthdate, @xdate) - CASE WHEN (MONTH(@birthdate) > MONTH(@xdate)) OR (MONTH(@birthdate) = MONTH(@xdate) AND DAY(@birthdate) > DAY(@xdate)) 
THEN 1 
ELSE 0 
END 

PRINT @years


I hope this helps to solve your problem.
I you have 25 years and 4 month it will show you 25 years.

Regards,
new guy wtfn00b.
Re: how to get birth date if age is 27 years 6 months & 22 days [message #529391 is a reply to message #529390] Mon, 31 October 2011 09:16 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
floor(months_between(SYSDATE,DOB)/12) age
How to calculate exact age [message #622926 is a reply to message #527976] Tue, 02 September 2014 02:33 Go to previous messageGo to next message
mastansky@gmail.com
Messages: 13
Registered: July 2013
Location: INDIA
Junior Member

Select trunc(months_between(sysdate,dob)/12) year,
trunc(mod(months_between(sysdate,dob),12)) month,
trunc(Add_Months(sysdate,-Trunc(months_between(sysdate,dob)))-dob) DAY
From (Select to_date('19052014','DDMMYYYY') dob From Dual)
/


Lalit : I formatted your code and added code tags, see the difference now :

SELECT Trunc(Months_between(SYSDATE, dob) / 12) 
       year, 
       Trunc(MOD(Months_between(SYSDATE, dob), 12)) 
       month, 
       Trunc(Add_months(SYSDATE, -Trunc(Months_between(SYSDATE, dob))) - dob) 
       DAY 
FROM   (SELECT To_date('19052014', 'DDMMYYYY') dob 
        FROM   dual) 

/

[Updated on: Tue, 02 September 2014 02:52] by Moderator

Report message to a moderator

Re: How to calculate exact age [message #622928 is a reply to message #622926] Tue, 02 September 2014 02:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

If you have tried to help OP who asked this question almost 3 years ago, that's fine, however, you did not add anything to the thread since it was already answered along with so many other hints.
Re: How to calculate exact age [message #622944 is a reply to message #622926] Tue, 02 September 2014 06:37 Go to previous message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

What is your goal to answer to a very old topic that has been answered?
What does your (bad formatted) query add to the previous answers?

Previous Topic: How works add column to table
Next Topic: Calender from 1-Jan-0001 Till 31-Dec-9999
Goto Forum:
  


Current Time: Mon Aug 04 03:30:46 CDT 2025