Home » SQL & PL/SQL » SQL & PL/SQL » Function for getting age in Years / Months / Days (merged)
|
|
|
|
Re: query [message #387574 is a reply to message #387558] |
Thu, 19 February 2009 23:04   |
|
select to_Number(substr(to_char(sysdate,'dd/mm/yyyy'),1,2)) day from dual;
DAY
20
select to_Number(substr(to_char(sysdate,'dd/mm/yyyy'),4,2)) MONTH from dual;
MONTH
2
select upper(to_Char(substr(to_char(sysdate,'dd/mon/yyyy'),4,3))) MONTH from dual;
MON
FEB
select to_Number(substr(to_char(sysdate,'dd/mm/yyyy'),7,4)) YEAR from dual;
YEAR
2009
CVS
|
|
|
Re: query [message #387577 is a reply to message #387574] |
Thu, 19 February 2009 23:19   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
Don't use SUBSTR function, use only TO_CHAR with 'DD','MM','MON','YYYY' instead of 'DD-MM-YYYY'
Thanks
Trivendra
|
|
|
Re: query [message #387584 is a reply to message #387558] |
Thu, 19 February 2009 23:34   |
ecearund
Messages: 5 Registered: February 2009 Location: Bangalore
|
Junior Member |
|
|
Hi,
Dont use substr function unnecessarily, it will suppress the performance.
Try to use to_char function itself.
Eg.
select to_char(sysdate,'dd') from dual;
|
|
|
Re: query [message #387585 is a reply to message #387574] |
Thu, 19 February 2009 23:37   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
hi,
i want in another scenario like if aperson is born on 23-09-1986. i want his current age as 23years like years seperate,
days seperate,months seperate in a query.is it possible.can anyone help.
|
|
|
Re: query [message #387587 is a reply to message #387585] |
Thu, 19 February 2009 23:40   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
The solution is it self in the post, Try with the given examples. If you are not able to create desired result, post your SQL query(that you have tried) so we can help you.
Thanks
Trivendra
|
|
|
Re: query [message #387589 is a reply to message #387587] |
Thu, 19 February 2009 23:50   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
select to_char(sysdate-'19-FEB-86','dd') from dual.
it is throwing exception like invalid number while using
|
|
|
Re: query [message #387590 is a reply to message #387589] |
Thu, 19 February 2009 23:53   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
'19-FEB-86' is a string not date, and always take year in four digit, as 86 can be 2086 or 1986.
Convert '19-FEB-86' to date by TO_DATE function and then try.
But why are converting sysdate-'19-FEB-86' in 'DD', what you are trying to achive.
Thanks
Trivendra
[Updated on: Thu, 19 February 2009 23:56] Report message to a moderator
|
|
|
Re: query [message #387592 is a reply to message #387590] |
Fri, 20 February 2009 00:00   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
select to_char(sysdate-to_date('12-FEB-1986')) from dual;
it is giving like 8409.48142361111111111111111111111111111
is it correct?
|
|
|
|
Re: query [message #387596 is a reply to message #387558] |
Fri, 20 February 2009 00:08   |
|
Here you go,
Alter a/c to your requirements,
select to_char(hiredate,'DAY') Days,to_char(hiredate,'MONTH') Months,to_char(hiredate,'YYYY') Year from emp
Regards,
Ashoka BL
|
|
|
Re: query [message #387597 is a reply to message #387592] |
Fri, 20 February 2009 00:14   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
rajasekhar857 wrote on Fri, 20 February 2009 07:00 | select to_char(sysdate-to_date('12-FEB-1986')) from dual;
it is giving like 8409.48142361111111111111111111111111111
is it correct?
|
I would believe Oracle, that it is correct number of days between those two dates.
However, months/years do not have constant number of days.
For obtaining correct number of months/years, have a look at MONTHS_BETWEEN function (and realize, that year has always 12 months).
|
|
|
Re: query [message #387599 is a reply to message #387558] |
Fri, 20 February 2009 00:16   |
|
One More query for your requirement,
Quote: |
hi,
i want in another scenario like if aperson is born on 23-09-1986. i want his current age as 23years like years seperate,
days seperate,months seperate in a query.is it possible.can anyone help.
|
SELECT (TO_CHAR (SYSDATE, 'YYYY') - TO_CHAR (hiredate, 'YYYY')) years,
ABS ((TO_CHAR (SYSDATE, 'MM') - TO_CHAR (hiredate, 'MM'))) months,
ABS ((TO_CHAR (SYSDATE, 'DD') - TO_CHAR (hiredate, 'DD'))) days
FROM emp
Hope it helps you..
Regards,
Ashoka BL
Bangalore
|
|
|
Re: query [message #387600 is a reply to message #387599] |
Fri, 20 February 2009 00:31   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
ashoka_bl wrote on Fri, 20 February 2009 07:16 | One More query for your requirement,
SELECT (TO_CHAR (SYSDATE, 'YYYY') - TO_CHAR (hiredate, 'YYYY')) years,
ABS ((TO_CHAR (SYSDATE, 'MM') - TO_CHAR (hiredate, 'MM'))) months,
ABS ((TO_CHAR (SYSDATE, 'DD') - TO_CHAR (hiredate, 'DD'))) days
FROM emp
Hope it helps you..
|
How do you think using ABS on the diff in days/months would ever yield the correct result?!
|
|
|
Re: query [message #387601 is a reply to message #387599] |
Fri, 20 February 2009 00:31   |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
@ashoka_bl,
I think your query is completly wrong.
SQL> with dat as (select to_date('13/11/1956','DD/MM/YYYY') dat from dual)
2 SELECT (TO_CHAR (SYSDATE, 'YYYY') - TO_CHAR (dat, 'YYYY')) years,
3 ABS ((TO_CHAR (SYSDATE, 'MM') - TO_CHAR (dat, 'MM'))) months,
4 ABS ((TO_CHAR (SYSDATE, 'DD') - TO_CHAR (dat, 'DD'))) days
5 from dat
6 /
YEARS MONTHS DAYS
---------- ---------- ----------
53 9 7
When my age is:
SQL> @age
Enter value for date_de_naissance: 13/11/1956
Date de naissance : 13/11/1956 - Age : 52 ans 3 mois 7 jours
Regards
Michel
|
|
|
|
|
Re: query [message #387614 is a reply to message #387558] |
Fri, 20 February 2009 01:14   |
|
Hi,
Check out this...
SELECT TO_CHAR (TRUNC (SYSDATE), 'DD-MON-YYYY') today,
TO_CHAR (TO_DATE ('13-Nov-1956'), 'DD-MON-YYYY') birthday,
TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE), TO_DATE ('13-Nov-1956')) / 12
) years,
MOD (TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE), TO_DATE ('13-Nov-1956'))),
12
) months,
TRUNC (SYSDATE)
- ADD_MONTHS ('13-Nov-1956',
TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE),
TO_DATE ('13-Nov-1956')
)
)
) days
FROM DUAL
Lets hope that this will give the correct results...
Regards,
Ashoka BL
|
|
|
Re: query [message #387622 is a reply to message #387614] |
Fri, 20 February 2009 01:36   |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
A small improvement is required:
SQL> SELECT TO_CHAR (TRUNC (SYSDATE), 'DD-MON-YYYY') today,
2 TO_CHAR (TO_DATE ('13-Nov-1956'), 'DD-MON-YYYY') birthday,
3 TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE), TO_DATE ('13-Nov-1956')) / 12
4 ) years,
5 MOD (TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE), TO_DATE ('13-Nov-1956'))),
6 12
7 ) months,
8 TRUNC (SYSDATE)
9 - ADD_MONTHS ('13-Nov-1956',
10 TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE),
11 TO_DATE ('13-Nov-1956')
12 )
13 )
14 ) days
15 FROM DUAL
16 /
TO_CHAR (TO_DATE ('13-Nov-1956'), 'DD-MON-YYYY') birthday,
*
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected
Regards
Michel
|
|
|
|
|
Re: query [message #387642 is a reply to message #387558] |
Fri, 20 February 2009 03:05   |
|
Ohh..
I just told because i am not getting any errors and the output which i got was correct.
Regards,
Ashoka BL
|
|
|
Re: query [message #387644 is a reply to message #387558] |
Fri, 20 February 2009 03:13   |
|
Check out the latest one..
I think this will work properly...
SELECT TO_CHAR (TRUNC (SYSDATE), 'YYYYMMDD') today,
TO_CHAR (TO_DATE ('19561113', 'YYYYMMDD'), 'YYYYMMDD') birthday,
TRUNC ( MONTHS_BETWEEN (TRUNC (SYSDATE),
TO_DATE ('19561113', 'YYYYMMDD')
)
/ 12
) years,
MOD (TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE),
TO_DATE ('19561113', 'YYYYMMDD')
)
),
12
) months,
TRUNC (SYSDATE)
- ADD_MONTHS (TO_DATE ('19561113', 'YYYYMMDD'),
TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE),
TO_DATE ('19561113', 'YYYYMMDD')
)
)
) days
FROM DUAL
Regards,
Ashoka BL
Bengaluru
|
|
|
|
Re: query [message #387653 is a reply to message #387558] |
Fri, 20 February 2009 03:33   |
|
That's cool.....
how do we write this who doesn't use gregorian calendar ??Any clues ?? I want to make it general and i want your one more SMILEY saying that IT'S CORRECT !!
Regards,
Ashoka BL
|
|
|
|
|
|
Re: query [message #387676 is a reply to message #387558] |
Fri, 20 February 2009 04:48   |
|
rajasekhar857,
Post us what have you tried so far !!
I believe that using the query which i posted you can easily convert it to a stored function ( i think you are reffering to a Stored Procedure).
And also let us know that whatever we gave the solution,have you tried and verified whether its giving you the correct results ?
Regards,
Ashoka BL
Bengaluru
|
|
|
|
|
Function for getting age in Years / Months / Days [message #387692 is a reply to message #387558] |
Fri, 20 February 2009 05:35   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
hi,
using a Function for getting age in Years / Months / Days.
first we have to go fro date
later based on that date we have to convert that in all aspects like years,months,days
my o/p should be like this
9 yrs 5 month(s) 3 day(s).
initially i have used the following sql query for it.
SELECT TO_CHAR (TRUNC (SYSDATE), 'DD-MON-YYYY') today,
TO_CHAR (TO_DATE ('13-Nov-1956'), 'DD-MON-YYYY') birthday,
TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE), TO_DATE ('13-Nov-1956')) / 12
) years,
MOD (TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE), TO_DATE ('13-Nov-1956'))
12
) months,
TRUNC (SYSDATE)
- ADD_MONTHS ('13-Nov-1956',
TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE),
TO_DATE ('13-Nov-1956')
)
)
) days
FROM DUAL;
|
|
|
Re: Function for getting age in Years / Months / Days [message #387694 is a reply to message #387692] |
Fri, 20 February 2009 05:40   |
|
Comeon....
Why are u opening new a post...you could have just put his in the Original Post,
We gave you the SQL Which you wanted ( although u didn't try yourself at all..)
Now can't you put that in a Function or Procedure ???
Quote: |
initially i have used the following sql query for it.
|
But it seems that YOU HAVE COPIED IT FROM HERE !!!
Regards,
Ashoka BL
Bengaluru
|
|
|
|
Re: Function for getting age in Years / Months / Days [message #387698 is a reply to message #387692] |
Fri, 20 February 2009 05:44   |
|
You have to just put this SQL inside a function,
You know how to create a function rite..if so then its very simple, all i wanted is that you could try so tht you will know how to do it,
Just asking here for everything is not a good idea.
Google for "Oracle Functions,Stored Procedures",you will get lot of links...
|
|
|
Re: Function for getting age in Years / Months / Days (merged 4) [message #387704 is a reply to message #387692] |
Fri, 20 February 2009 05:55   |
|
TAKE IT AND ENJOY......................
AND PLEASE LET US KNOW WHETHER YOU CAN EXECUTE THIS FUNCTION TO TEST..IF NOT WE ARE THERE TO HELP.......
CREATE OR REPLACE FUNCTION year_month_days (i_date DATE)
RETURN VARCHAR2
IS
return_text VARCHAR2 (300);
l_year NUMBER;
l_month NUMBER;
l_days NUMBER;
l_date1 VARCHAR2 (8);
BEGIN
l_date1 := TO_CHAR (i_date, 'YYYYMMDD');
SELECT TRUNC ( MONTHS_BETWEEN (TRUNC (SYSDATE),
TO_DATE (l_date1, 'YYYYMMDD')
)
/ 12
) years,
MOD (TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE),
TO_DATE (l_date1, 'YYYYMMDD')
)
),
12
) months,
TRUNC (SYSDATE)
- ADD_MONTHS (TO_DATE (l_date1, 'YYYYMMDD'),
TRUNC (MONTHS_BETWEEN (TRUNC (SYSDATE),
TO_DATE (l_date1, 'YYYYMMDD')
)
)
) days
INTO l_year,
l_month,
l_days
FROM DUAL;
return_text :=
l_year || ' Years ' || l_month || ' month(s) ' || l_days || 'day(s)';
RETURN return_text;
END year_month_days;
/
If you can observe, just compare the SQL which i gave and the fucntion which i gave, and see how simple if you know how to write the function..
Regards,
Ashoka BL
Bengaluru
|
|
|
|
Goto Forum:
Current Time: Wed Feb 12 08:12:51 CST 2025
|