Home » SQL & PL/SQL » SQL & PL/SQL » to get actual age (merged)
to get actual age (merged) [message #196857] Sun, 08 October 2006 12:51 Go to next message
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 #196864 is a reply to message #196857] Sun, 08 October 2006 16:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
subtract from SYSDATE the Date Of Birth IDOB).
Re: to get actual age [message #196870 is a reply to message #196857] Sun, 08 October 2006 21:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #196890 is a reply to message #196889] Mon, 09 October 2006 01:17 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thank you very much maher, ab_trivedi & anacedent Cool
Re: to get actual age [message #196940 is a reply to message #196857] Mon, 09 October 2006 05:07 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi

to get the actual age

in the years

check this example

select round((sysdate-dob)/365,0) as age from emp.

regards

Muhammad Shahid Mughal
Faisalabad
Re: to get actual age [message #196976 is a reply to message #196940] Mon, 09 October 2006 07:34 Go to previous messageGo to next message
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 #197024 is a reply to message #196976] Mon, 09 October 2006 10:53 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello frank,
should i have to put 365 or 366 days.
i wouldn't count automatically.
or i should follow maher's step.
thankxx
Re: to get actual age [message #197052 is a reply to message #196976] Mon, 09 October 2006 14:26 Go to previous messageGo to next message
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..
Re: to get actual age [message #197056 is a reply to message #197052] Mon, 09 October 2006 15:44 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, if everything else fails, you could always call that specific customer and ask him "Hello there, how old are you?".
Re: to get actual age [message #197110 is a reply to message #197056] Tue, 10 October 2006 01:59 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
rofl
to get actual age [message #198028 is a reply to message #196857] Fri, 13 October 2006 21:38 Go to previous messageGo to next message
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 #198030 is a reply to message #198028] Fri, 13 October 2006 21:56 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try TO_DATE instead of TO_CHAR

Ross Leishman
Re: to get actual age [message #198031 is a reply to message #198030] Fri, 13 October 2006 22:04 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello rleishman
can u please tell what i had done wrong in this syntax.
dnt try to get excuse. Mad
if this syntax is right why i m not getting value???
please make my syntax correct.
thanxxxx
Re: to get actual age [message #198035 is a reply to message #198031] Fri, 13 October 2006 23:17 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
i hope u are fine.

you are using this syntax

select trunc((to_char('10/14/2006', 'mm/dd/yyyy')-to_char('02/08/1980', 'mm/dd/yyyy'))/10000)as age

-------------------------------------------

you should use like this

select trunc((sysdate-birthday)/365) as age from tabname;
for weeks devide with 52
for months devide with 12
for days use no division just add 1
for hours multiply with 24
for minutes multiply with 24*60
i hope this will solve your problem


regards

Muhammad Shahid Mughal
Re: to get actual age [message #198058 is a reply to message #198035] Sat, 14 October 2006 03:23 Go to previous messageGo to next message
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 http://www.orafaq.com/forum/fa/1604/0/.

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 #198063 is a reply to message #198031] Sat, 14 October 2006 03:50 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
ashish_pass1 wrote on Sat, 14 October 2006 05:04

can u please tell what i had done wrong in this syntax.
"u" is not a member here. Use plain English next time. Save your MSN lingo for MSN! Besides, he already hinted to use TO_DATE instead of TO_CHAR.

ashish_pass1 wrote on Sat, 14 October 2006 05:04

dnt try to get excuse. Mad
Does not compute. What on earth are you talking about?
ashish_pass1 wrote on Sat, 14 October 2006 05:04

if this syntax is right why i m not getting value???
Obviously it is not right.

Some tips:

  1. Don't start a new topic on the same subject.
  2. Use plain English. No "U", no "dnt", no IM speak.
  3. Be polite.
  4. Above all: read the tips and tricks sticky. It is the first topic in this forum.


I already gave you a solution in your previous topic. I have merged them.

About the ampersant (&-sign): it is used for substitution variables in tools like SQL*Plus. SYSDATE, is an oracle built-in. It is already a value. No need for an ampersant. SYSDATE is already a DATE. Keep it that way and don't convert it to a string via TO_CHAR. You want to convert your date of birth from a string to a date. TO a DATE. Would that be TO_DATE or TO_CHAR? Wink

I have merged the topics. Again: read the sticky (tips&tricks).

MHE


Re: to get actual age [message #198071 is a reply to message #198063] Sat, 14 October 2006 06:07 Go to previous message
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..?
Previous Topic: inner and outer block exceptions
Next Topic: Which of the following is best- MERGE or update/insert trigger?
Goto Forum:
  


Current Time: Tue Feb 18 08:59:28 CST 2025