OraFAQ Forum: SQL & PL/SQL » How to calculate exact age

Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate exact age (Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to calculate exact age [message #527976]

Fri, 21 October 2011 02:20

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

Report message to a moderator

Re: How to calculate exact age [message #527979 is a reply to message #527976]

Fri, 21 October 2011 02:30

Michel Cadot
Messages: 68767
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

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Give you a hint. Read up on the months_between function and some simple math.

Report message to a moderator

Re: How to calculate exact age [message #528213 is a reply to message #528122]

Sun, 23 October 2011 03:07

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

Michel Cadot
Messages: 68767
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

Report message to a moderator

Re: How to calculate exact age [message #528217 is a reply to message #528215]

Sun, 23 October 2011 03:36

Michel Cadot
Messages: 68767
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

Report message to a moderator

Re: How to calculate exact age [message #529013 is a reply to message #528217]

Fri, 28 October 2011 03:25

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);

Report message to a moderator

Re: How to calculate exact age [message #529014 is a reply to message #529013]

Fri, 28 October 2011 03:44

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Convert 27.5 to months and use the add_months function.

Report message to a moderator

Re: How to calculate exact age [message #529015 is a reply to message #529013]

Fri, 28 October 2011 03:51

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).

Report message to a moderator

Re: How to calculate exact age [message #529018 is a reply to message #529013]

Fri, 28 October 2011 04:00

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.

Report message to a moderator

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

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

Report message to a moderator

Re: How to calculate exact age [message #529113 is a reply to message #529018]

Fri, 28 October 2011 14:00

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

Report message to a moderator

Re: How to calculate exact age [message #529114 is a reply to message #529113]

Fri, 28 October 2011 14:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ADD_MONTHS(SYSDATE-22, -((27*12)+6))

Report message to a moderator

Re: How to calculate exact age [message #529115 is a reply to message #529113]

Fri, 28 October 2011 14:05

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

Report message to a moderator

Re: How to calculate exact age [message #529116 is a reply to message #529115]

Fri, 28 October 2011 14:12

qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN

Senior Member

if age is 26.50 than how to calculate date of birth

Report message to a moderator

Re: How to calculate exact age [message #529117 is a reply to message #529116]

Fri, 28 October 2011 14:16

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!

Report message to a moderator

Re: How to calculate exact age [message #529119 is a reply to message #529117]

Fri, 28 October 2011 14:22

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

Report message to a moderator

Re: How to calculate exact age [message #529121 is a reply to message #529119]

Fri, 28 October 2011 14:25

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

Report message to a moderator

Re: How to calculate exact age [message #529122 is a reply to message #529119]

Fri, 28 October 2011 14:25

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

Report message to a moderator

Re: How to calculate exact age [message #529124 is a reply to message #529119]

Fri, 28 October 2011 14:27

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.

Report message to a moderator

Re: How to calculate exact age [message #529125 is a reply to message #529122]

Fri, 28 October 2011 14:27

Bill B
Messages: 1971
Registered: December 2004

Senior Member

While I got tired and answered it, this has to be homework.

Report message to a moderator

Re: How to calculate exact age [message #529126 is a reply to message #529125]

Fri, 28 October 2011 14:28

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%

Report message to a moderator

Re: How to calculate exact age [message #529128 is a reply to message #529125]

Fri, 28 October 2011 14:33

Littlefoot
Messages: 21826
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.

Report message to a moderator

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

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

Report message to a moderator

Re: How to calculate exact age [message #529134 is a reply to message #529119]

Fri, 28 October 2011 15:03

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

Bill B
Messages: 1971
Registered: December 2004

Senior Member

floor(months_between(SYSDATE,DOB)/12) age

Report message to a moderator

How to calculate exact age [message #622926 is a reply to message #527976]

Tue, 02 September 2014 02:33

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

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.

Report message to a moderator

Re: How to calculate exact age [message #622944 is a reply to message #622926]

Tue, 02 September 2014 06:37

Michel Cadot
Messages: 68767
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?

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 24 23:12:36 CDT 2025