Home » SQL & PL/SQL » SQL & PL/SQL » Function for getting age in Years / Months / Days (merged)
Function for getting age in Years / Months / Days (merged) [message #387558] Thu, 19 February 2009 22:29 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
can i write in a query like a persons date of birth to be seperated as days seperate, months seperate,years seperate.

ex:12-aug-1984 to be happen
Re: query [message #387559 is a reply to message #387558] Thu, 19 February 2009 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.


>can i write in a query like a persons date of birth to be seperated as days seperate, months seperate,years seperate.
I give up. Can you?

I do not understand exactly what is your input,
or what is the expected/desired results.
Re: query [message #387560 is a reply to message #387559] Thu, 19 February 2009 22:35 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
look for TO_CHAR function.
Re: query [message #387565 is a reply to message #387560] Thu, 19 February 2009 22:42 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
ca you show me with an example how to do that using to_char
Re: query [message #387574 is a reply to message #387558] Thu, 19 February 2009 23:04 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

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 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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 Go to previous messageGo to next message
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 #387595 is a reply to message #387592] Fri, 20 February 2009 00:05 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
TRUNC sysdate.
Re: query [message #387596 is a reply to message #387558] Fri, 20 February 2009 00:08 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 #387602 is a reply to message #387558] Fri, 20 February 2009 00:34 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Ya Michel,

I agree...i didn't tested properly, now i am doing it...

Regards,
Ashoka BL
Re: query [message #387603 is a reply to message #387558] Fri, 20 February 2009 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rajasekhar857,

This exercise has been asked and answered many times.
Have a look at date functions like MONTHS_BETWEEN

Regards
Michel
Re: query [message #387614 is a reply to message #387558] Fri, 20 February 2009 01:14 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 #387634 is a reply to message #387558] Fri, 20 February 2009 02:40 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Michel,

It got executed here. My Oracle version is 10.2.0.1

Regards,
Ashoka BL
Bengaluru

Re: query [message #387638 is a reply to message #387634] Fri, 20 February 2009 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You got it does not mean it is correct.
And it is not as it depends on your NLS settings.


Regards
Michel
Re: query [message #387642 is a reply to message #387558] Fri, 20 February 2009 03:05 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #387649 is a reply to message #387644] Fri, 20 February 2009 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now it is correct (for those who use gregorian calendar Smile ).

Regards
Michel
Re: query [message #387653 is a reply to message #387558] Fri, 20 February 2009 03:33 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #387664 is a reply to message #387653] Fri, 20 February 2009 04:07 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how to write the above requirement using a stored function
Re: query [message #387667 is a reply to message #387664] Fri, 20 February 2009 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajasekhar857 wrote on Fri, 20 February 2009 11:07
how to write the above requirement using a stored function

Post what you already tried. It seems you did nothing so far.

Regards
Michel
Re: query [message #387669 is a reply to message #387653] Fri, 20 February 2009 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ashoka_bl wrote on Fri, 20 February 2009 10: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

You have to study each calendar, not each one contains 12 months in a year, some contains a different number of months each year!

Regards
Michel

Re: query [message #387676 is a reply to message #387558] Fri, 20 February 2009 04:48 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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
Re: query [message #387684 is a reply to message #387676] Fri, 20 February 2009 05:15 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yeah i have tried its working fine.can you help me out in a stored function how it shows
Re: query [message #387690 is a reply to message #387558] Fri, 20 February 2009 05:25 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Can you let us know what have you tried so far to convert into a Function, we can dfntly help to improve..

Function for getting age in Years / Months / Days [message #387692 is a reply to message #387558] Fri, 20 February 2009 05:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #387696 is a reply to message #387694] Fri, 20 February 2009 05:42 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yeah its yours only.i am trying for it.can you help me just as i mentioned in that desired o/p using a function
Re: Function for getting age in Years / Months / Days [message #387698 is a reply to message #387692] Fri, 20 February 2009 05:44 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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
Re: Function for getting age in Years / Months / Days (merged 4) [message #387712 is a reply to message #387704] Fri, 20 February 2009 06:28 Go to previous messageGo to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
thank you very much for your help.thanks a lot
Previous Topic: Hierachical query based on column value
Next Topic: Updating 'LONG' column
Goto Forum:
  


Current Time: Thu Dec 08 08:06:23 CST 2016

Total time taken to generate the page: 0.11754 seconds