to get actual age (merged) [message #196857] |
Sun, 08 October 2006 12:51  |
ashish_pass1
Messages: 114 Registered: August 2006 Location: delhi
|
Senior Member |
|
|
sir,
suppose i m having d_o_b of a client. but i want to get actual
age of him(age till date means compare it by localtimestamp).
what would be the procedure?
can any one help me to over by this problem
thanxx in advance
|
|
|
|
Re: to get actual age [message #196870 is a reply to message #196857] |
Sun, 08 October 2006 21:41   |
ab_trivedi
Messages: 460 Registered: August 2006 Location: Pune, India
|
Senior Member |
|
|
Hi Ashish,
You can use the minus from sysdate to client d_o_b and see there are lot of date functions available with Oracle. Read them and use them ...
Bye
Ashu
|
|
|
Re: to get actual age [message #196889 is a reply to message #196870] |
Mon, 09 October 2006 01:08   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
I'd use the MONTHS_BETWEEN built-in:
SQL> SELECT trunc(months_between(sysdate,d_o_b)/12)||' years and '||
2 trunc(mod(months_between(sysdate,d_o_b),12))||' months.' age
3 FROM ( SELECT to_date('01/21/1976','MM/DD/YYYY') d_o_b
4 FROM dual
5 )
6 /
AGE
------------------------------
30 years and 8 months.
A pure subtraction (sysdate-d_o_b) returns the number of days between the two dates. Months_between is easier to convert to years.
MHE
[Updated on: Mon, 09 October 2006 01:11] Report message to a moderator
|
|
|
|
|
Re: to get actual age [message #196976 is a reply to message #196940] |
Mon, 09 October 2006 07:34   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
SQL> select round((sysdate - to_date('01-01-2006', 'dd-mm-yyyy'))/365, 0) from dual;
ROUND((SYSDATE-TO_DATE('01-01-2006','DD-MM-YYYY'))/365,0)
---------------------------------------------------------
1
Round won't do.
Neither will 365: ever heard of leap years?
Go with Maaher's solution.
[Updated on: Mon, 09 October 2006 07:37] Report message to a moderator
|
|
|
|
Re: to get actual age [message #197052 is a reply to message #196976] |
Mon, 09 October 2006 14:26   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
ashish_pass1 wrote on Mon, 09 October 2006 17:53 | <snip>
or i should follow maher's step.
|
Frank wrote on Mon, 09 October 2006 14:34 |
Go with Maaher's solution.
|
Gosh, I wouldn't know what you should do..
|
|
|
|
|
to get actual age [message #198028 is a reply to message #196857] |
Fri, 13 October 2006 21:38   |
ashish_pass1
Messages: 114 Registered: August 2006 Location: delhi
|
Senior Member |
|
|
sir,
i want to kn what i did wrong?
please make my query correct.
SQL>select trunc((to_char(&sysdate, 'mm/dd/yyyy')-to_char(&birthday, 'mm/dd/yyyy'))/10000)as age
from dual;
Enter value for sysdate: '10/14/2006'
Enter value for birthday: '02/08/1980'
select trunc((to_char('10/14/2006', 'mm/dd/yyyy')-to_char('02/08/1980', 'mm/dd/yyyy'))/10000)as age
*
ERROR at line 1:
ORA-01722: invalid number
thanxx i advance
|
|
|
|
|
|
Re: to get actual age [message #198058 is a reply to message #198035] |
Sat, 14 October 2006 03:23   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I can't believe you didn't manage to get the answer to the same question you asked a few days ago .
By the way, what does it mean "dnt try to get excuse"? Who? You didn't try, or Ross shouldn't try?
His answer was fair enough: you are converting a string back into string. Besides that, SYSDATE IS of a DATE datatype - it doesn't need conversion. Moreover, what should "&sysdate" be?
Basically, this is what you need:
SELECT SYSDATE - TO_DATE('&birthday', 'dd.mm.yyyy') FROM dual;
Check Shahidmughal's answer to see how to get months, days etc.
[Updated on: Sat, 14 October 2006 03:51] by Moderator Report message to a moderator
|
|
|
|
Re: to get actual age [message #198071 is a reply to message #198063] |
Sat, 14 October 2006 06:07  |
amul
Messages: 252 Registered: April 2001 Location: Chennai
|
Senior Member |
|
|
no one can spoon feed you better than maaher..but still you are asking for it..why dont you try modifying it according to your requirements..?
|
|
|