Home » SQL & PL/SQL » SQL & PL/SQL » I need to sort out age from a list.
I need to sort out age from a list. [message #298341] Tue, 05 February 2008 17:09 Go to next message
crystalarun
Messages: 2
Registered: February 2008
Junior Member
I have a list of patients with me with date of birth. I need to calclutate age for this i did

select pid,
trim(upper(pname)) as name,
(extract(year from sysdate)-extract(year from dobirth)) as age_from_years
from patient;

Now I want

1) to sort all above 20 yrs.
2) to find age more precisely by using month function as well.

How can I do that. please help

[Updated on: Tue, 05 February 2008 17:12]

Report message to a moderator

Re: I need to sort out age from a list. [message #298345 is a reply to message #298341] Tue, 05 February 2008 17:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You want the MONTHS_BETWEEN function for calculating age.
Re: I need to sort out age from a list. [message #298347 is a reply to message #298345] Tue, 05 February 2008 17:52 Go to previous messageGo to next message
crystalarun
Messages: 2
Registered: February 2008
Junior Member
How is it possible. can u show with the help of an example syntax please
Re: I need to sort out age from a list. [message #298349 is a reply to message #298341] Tue, 05 February 2008 17:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Are you incapable or unwilling to Read The Fine Manual?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions089.htm#sthref1622
Re: I need to sort out age from a list. [message #298356 is a reply to message #298341] Tue, 05 February 2008 22:25 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
select trunc(months_between(sysdate, 
dobirth)/12) 
as years, 
mod(trunc(months_between(sysdate,dobirth)),12) 
as months,
trunc(sysdate - 
add_months(dobirth, 
months_between(sysdate,dobirth))) 
as days 
from patient
where trunc(months_between(sysdate, 
dobirth)/12)>20; 



regards,

Re: I need to sort out age from a list. [message #298391 is a reply to message #298356] Wed, 06 February 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mshrkshl, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

What you posted is unreadable and so useless.

Regards
Michel

[Updated on: Wed, 06 February 2008 00:41]

Report message to a moderator

Re: I need to sort out age from a list. [message #298412 is a reply to message #298341] Wed, 06 February 2008 01:30 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SQL> select name,dobirth from patient;

NAME       DOBIRTH
---------- ---------
Martin     12-JUN-58
John       02-JUL-65


select name,dobirth,trunc(months_between(sysdate,dobirth)/12)||' years '||
 mod(trunc(months_between(sysdate,dobirth)),12)||' months '||
 trunc(sysdate -add_months(dobirth,months_between(sysdate,dobirth)))||' days'  as   age
 from patient
 where months_between(sysdate,
 dobirth)/12>20
 /


NAME       DOBIRTH   AGE
---------- --------- ---------------------------------
Martin     12-JUN-58 49 years 7 months 25 days
John       02-JUL-65 42 years 7 months 4 days


regards,
Re: I need to sort out age from a list. [message #298418 is a reply to message #298412] Wed, 06 February 2008 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you think this a pretty printed query?

Separate logical parts, don't go in new in a middle of an expression (but for the previous point), indent, use space in right place and remove multiple useless ones...
This is unreadable.

Regards
Michel

Re: I need to sort out age from a list. [message #298440 is a reply to message #298418] Wed, 06 February 2008 02:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to Michel's points:
it is not even a full copy-paste.
It has a leading space before the final /
Re: I need to sort out age from a list. [message #298445 is a reply to message #298341] Wed, 06 February 2008 02:16 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear Michel and frank,

May you post the same in prescribed format?

please, Embarassed
Re: I need to sort out age from a list. [message #298449 is a reply to message #298445] Wed, 06 February 2008 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You could use SQL Formatter

Regards
Michel
Re: I need to sort out age from a list. [message #298452 is a reply to message #298341] Wed, 06 February 2008 02:29 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
thanks,

I have now all your points.

Surprised

SELECT NAME,
       DoBirth,
       Trunc(Months_between(SYSDATE,DoBirth) / 12)
       ||' years '
       ||Mod(Trunc(Months_between(SYSDATE,DoBirth)),12)
       ||' months '
       ||Trunc(SYSDATE - Add_months(DoBirth,Months_between(SYSDATE,DoBirth)))
       ||' days' AS Age
FROM   Patient
WHERE  Months_between(SYSDATE,DoBirth) / 12 > 20
Re: I need to sort out age from a list. [message #298470 is a reply to message #298452] Wed, 06 February 2008 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you think it is easier to read? Thumbs Up

Regards
Michel

[Updated on: Wed, 06 February 2008 03:13]

Report message to a moderator

Re: I need to sort out age from a list. [message #298475 is a reply to message #298341] Wed, 06 February 2008 03:12 Go to previous message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Laughing
Previous Topic: getting error in UNIX
Next Topic: External table
Goto Forum:
  


Current Time: Sun Dec 04 06:50:19 CST 2016

Total time taken to generate the page: 0.08184 seconds