Home » Other » Training & Certification » How to find out my age in years, months, days upto till date
How to find out my age in years, months, days upto till date [message #295949] Thu, 24 January 2008 00:18 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Dear Friends,

How i can find out my age in years, months, days?

from till date

Welcome your suggestions.

Thanks

Mano
Re: How to find out my age in years, months, days upto till date [message #295951 is a reply to message #295949] Thu, 24 January 2008 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's not so difficult.
MONTHS_BETWEEN
MOD
TRUNC and TRUNC
minus

Regards
Michel




[Updated on: Thu, 24 January 2008 00:34]

Report message to a moderator

Re: How to find out my age in years, months, days upto till date [message #295954 is a reply to message #295949] Thu, 24 January 2008 00:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Do you want ORACLE to Predit your Age ? Smile
What did you try so far

Anyway try using Inbuilt sql functions .

Thumbs Up
Rajuvan
Re: How to find out my age in years, months, days upto till date [message #295970 is a reply to message #295949] Thu, 24 January 2008 01:08 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
declare
birthdate date :='23-jan-1979';
begin
dbms_output.put_line('Your age is 
'||(to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy'))||' years 
'||(to_char(sysdate,'MM')-to_char(birthdate,'MM'))||' months
'||(to_char(sysdate,'DD')-to_char(birthdate,'DD'))||' days');
end;
/
Your age is 
29 years 
0 months
1 days
Re: How to find out my age in years, months, days upto till date [message #295972 is a reply to message #295970] Thu, 24 January 2008 01:10 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

from Orafaq search page Smile
http://forums.oracle.com/forums/thread.jspa?messageID=1465273
Re: How to find out my age in years, months, days upto till date [message #295974 is a reply to message #295970] Thu, 24 January 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong:
SQL> declare
  2  birthdate date :='23-jan-1979';
  3  begin
  4  dbms_output.put_line('Your age is 
  5  '||(to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy'))||' years 
  6  '||(to_char(sysdate,'MM')-to_char(birthdate,'MM'))||' months
  7  '||(to_char(sysdate,'DD')-to_char(birthdate,'DD'))||' days');
  8  end;
  9  /
'||(to_char(sysdate,'MM')-to_char(birthdate,'MM'))||' months
                 *
ERROR at line 6:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 2

SQL> declare
  2  birthdate date := to_date('13/11/1956','DD/MM/YYYY');
  3  begin
  4  dbms_output.put_line('Your age is 
  5  '||(to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy'))||' years 
  6  '||(to_char(sysdate,'MM')-to_char(birthdate,'MM'))||' months
  7  '||(to_char(sysdate,'DD')-to_char(birthdate,'DD'))||' days');
  8  end;
  9  /
Your age is
52 years
-10 months
11 days

PL/SQL procedure successfully completed.

My age is 52 years minus 10 months!

Regards
Michel
Re: How to find out my age in years, months, days upto till date [message #295975 is a reply to message #295949] Thu, 24 January 2008 01:18 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

hI MSHRKSHL,
do you think ,
following is right ?

SQL> declare
  2  birthdate date :='31-dec-1979';
  3  begin
  4  dbms_output.put_line('Your age is
  5  '||(to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy'))||' years
  6  '||(to_char(sysdate,'MM')-to_char(birthdate,'MM'))||' months
  7  '||(to_char(sysdate,'DD')-to_char(birthdate,'DD'))||' days');
  8  end;
  9  /
Your age is
29 years
-11 months
-7 days

PL/SQL procedure successfully completed.

SQL>


Thumbs Up
Rajuvan.
Re: How to find out my age in years, months, days upto till date [message #295978 is a reply to message #295949] Thu, 24 January 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't want to have the pleasure to find it by yourself: How to get the difference between 2 dates in format years/months/days

Regards
Michel
Re: How to find out my age in years, months, days upto till date [message #295979 is a reply to message #295974] Thu, 24 January 2008 01:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Thu, 24 January 2008 08:17
My age is 52 years minus 10 months!
And, is it? Wink

MHE
Re: How to find out my age in years, months, days upto till date [message #295980 is a reply to message #295949] Thu, 24 January 2008 01:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

hi misragopal ,

Dont you think , use of MOD is more simple than using the complex query as in the link ?

Michel,

I think , need to use ADD_MONTHS Function to get the exact days .

Thumbs Up
Rajuvan
Re: How to find out my age in years, months, days upto till date [message #295982 is a reply to message #295980] Thu, 24 January 2008 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct! Missed it.

Regards
Michel

[Updated on: Thu, 24 January 2008 01:27]

Report message to a moderator

Re: How to find out my age in years, months, days upto till date [message #295986 is a reply to message #295949] Thu, 24 January 2008 01:52 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
i got the solution from this..


select trunc(months_between(to_date('01/06/2003','MM/DD/YYYY'),
to_date('10/15/1992','MM/DD/YYYY'))/12)
||' years '||
mod(trunc(months_between(to_date('01/06/2003','MM/DD/YYYY'),
to_date('10/15/1992','MM/DD/YYYY'))),12)
||' months '||
trunc(to_date('01/06/2003','MM/DD/YYYY') -
add_months(to_date('10/15/1992','MM/DD/YYYY'),
months_between(to_date('01/06/2003','MM/DD/YYYY'),
to_date('10/15/1992','MM/DD/YYYY'))))
||' days' "Year_Month_Day"
from dual;

Re: How to find out my age in years, months, days upto till date [message #295987 is a reply to message #295986] Thu, 24 January 2008 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i got the solution from this..

You meant from the link I posted.
Too bad you didn't get it by yourself.

Regadrs
Michel
Re: How to find out my age in years, months, days upto till date [message #295990 is a reply to message #295949] Thu, 24 January 2008 02:12 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hmhm...

You gave the link .
You tempted him with Answer .

Even now Calling it 'Bad' ? Smile

Most of the people posting here cannord afford to ignore the links like this.

Thumbs Up
Rajuvan.

[Updated on: Thu, 24 January 2008 02:13]

Report message to a moderator

Re: How to find out my age in years, months, days upto till date [message #295991 is a reply to message #295990] Thu, 24 January 2008 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't say it is bad in the meaning of wrong. I meant it was a pity he didn't find it by himself, it would be preferable he found it by himself... do you see my point?

Regards
Michel
Re: How to find out my age in years, months, days upto till date [message #295994 is a reply to message #295949] Thu, 24 January 2008 02:26 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hello friends,

sorry sorry..

i got solution from another forum only through google. i did not mentioned. really i would like to say sorry....

add_months giving results
Re: How to find out my age in years, months, days upto till date [message #296002 is a reply to message #295949] Thu, 24 January 2008 03:08 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes.But even i didn't really mean it .

Didn't you notcie smiley

Thumbs Up
Rajuvan.
Re: How to find out my age in years, months, days upto till date [message #296018 is a reply to message #295949] Thu, 24 January 2008 03:52 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
sorry Cool
Re: How to find out my age in years, months, days upto till date [message #296230 is a reply to message #295949] Fri, 25 January 2008 03:20 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
what about this ?
declare
birthdate date :='30-dec-1979';
dd number;
mm number;
yyyy number;
begin
select (to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy')) into yyyy from  dual;
select (to_char(sysdate,'MM')-to_char(birthdate,'MM')) into mm from dual;
select (to_char(sysdate,'DD')-to_char(birthdate,'DD')) into dd from dual;
if dd<0
then dd :=dd+30;
mm :=mm-1;
end if;
if mm<0
then mm :=mm+12;
yyyy :=yyyy-1;
end if;
dbms_output.put_line('Your age is
'||yyyy||' years
'||MM||' months
'||DD||' days');
end;

Your age is
28 years
0 months
25 days

PL/SQL procedure successfully completed.
Re: How to find out my age in years, months, days upto till date [message #296236 is a reply to message #296230] Fri, 25 January 2008 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't use PL/SQL when you can only use SQL
2/ Don't call SQL in PL/SQL when you can do it without SQL

Regards
Michel
Re: How to find out my age in years, months, days upto till date [message #296255 is a reply to message #296236] Fri, 25 January 2008 05:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't entirely agree with your points here Michel.
I think the very first one can also be 'Do it in pl/sql when you don't need any sql'
It more or less depends on where one will need the functionality: when called from a sql-query you should code it in SQL. When called from pl/sql, use pl/sql.
Re: How to find out my age in years, months, days upto till date [message #296256 is a reply to message #295949] Fri, 25 January 2008 05:56 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear Michel,

First of all to many thanks to you for your nice guidelines to all.

Regards,
mshrkshl

am i right now?
(only in pl/sql)

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  birthdate date :='30-dec-1979';
  3  dd number :=(to_char(sysdate,'DD')-to_char(birthdate,'DD'));
  4  mm number :=(to_char(sysdate,'MM')-to_char(birthdate,'MM'));
  5  yyyy number  :=(to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy'));
  6  begin
  7  if dd<0
  8  then dd :=dd+30;
  9  mm :=mm-1;
 10  end if;
 11  if mm<0
 12  then mm :=mm+12;
 13  yyyy :=yyyy-1;
 14  end if;
 15  dbms_output.put_line('Your age is
 16  '||yyyy||' years
 17  '||MM||' months
 18  '||DD||' days');
 19* end;
SQL> /
Your age is
28 years
0 months
25 days

PL/SQL procedure successfully completed.
Re: How to find out my age in years, months, days upto till date [message #296257 is a reply to message #296256] Fri, 25 January 2008 05:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Now your code assumes every month has the same number of days.
Use built-in functions. They account for leap-years, etc.
Re: How to find out my age in years, months, days upto till date [message #296260 is a reply to message #296256] Fri, 25 January 2008 06:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FRank gives you the answer.
Also indent your code, it will more readable, don't you think?

Regards
Michel
Previous Topic: Splitting a variable length delimited string into multiple strings
Next Topic: Materilized View
Goto Forum:
  


Current Time: Sat Dec 03 14:20:25 CST 2016

Total time taken to generate the page: 0.07390 seconds