Home » Other » Training & Certification » How to find out my age in years, months, days upto till date
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #295974 is a reply to message #295970] |
Thu, 24 January 2008 01:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/70674.jpg) |
rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India
|
Senior Member |
![i.rajavu@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
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](images/smiley_icons/icon_thumbsup.gif)
Rajuvan.
|
|
|
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #296230 is a reply to message #295949] |
Fri, 25 January 2008 03:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #296256 is a reply to message #295949] |
Fri, 25 January 2008 05:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
|
Goto Forum:
Current Time: Sun May 19 18:38:14 CDT 2024
|