Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2004
    Posts
    102

    Unanswered: Find the tenure Of a Employee

    Dear All,

    I want to find the Employee Tenure.

    Say, an employee DOJ is 01-OCT-09. I want to figure out his tenure at 30-NOV-10......[1 years 1 month]


    How i can do that ???...Pls help me.
    Working Together...

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Most comfortably by using MONTHS_BETWEEN function. Have a look into SQL*Reference Guide book for its syntax and examples. You may find it e.g. online on http://tahiti.oracle.com/

  3. #3
    Join Date
    Jul 2004
    Posts
    102
    thanks for your reply.

    Btw, the MONTHS_BETWEEN function returns number value but we need like [1 years 1 month]


    SELECT MONTHS_BETWEEN
    (TO_DATE('02-02-1995','MM-DD-YYYY'),
    TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
    FROM DUAL;

    Months
    ----------
    1.03225806
    Last edited by Momin; 11-22-10 at 05:46.
    Working Together...

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    As one year contains always 12 months in my universe, it is a matter of simple math (/, TRUNC or FLOOR, MOD) to convert number of months to number of years and months, is not it?

  5. #5
    Join Date
    Jul 2004
    Posts
    102
    As one year contains always 12 months in my universe, it is a matter of simple math (/, TRUNC or FLOOR, MOD) to convert number of months to number of years and months, is not it?
    Give me a favor boss.

    Write an example as i really don't understand how to ....
    Working Together...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Sample FUNCTION
    Code:
    QL>  CREATE OR REPLACE FUNCTION OUNCES_TO_POUNDS (RAW_OZ NUMBER)
      2  RETURN VARCHAR2
      3  AS
      4  POUNDS_OUNCE VARCHAR2(63);
      5  BEGIN
      6  POUNDS_OUNCE := TO_CHAR(TRUNC(RAW_OZ/16)) || ' Pounds';
      7  RETURN POUNDS_OUNCE;
      8  END OUNCES_TO_POUNDS;
      9  /
    
    Function created.
    
    SQL> select OUNCES_TO_POUNDS(32) from dual;
    
    OUNCES_TO_POUNDS(32)
    --------------------------------------------------------------------------------
    2 Pounds
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2004
    Posts
    102
    Is that function return 2 Pounds and 7 ounce;

    Although ounce to pound is according to a static value (16 ounce= 1 pound) but yead r different. some r 366 days and some r 365 days.

    I really need your help in this issue. Please help me.
    Working Together...

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    But years have the same number of months - 12. So, after counting the difference in months you may convert it to years/months.

    Just curious, what you do not understand. How to convert given number of months (e.g. 29) to years and months or how to write it in Oracle? Of course, the second task would mimic the way you counted it manually.

  9. #9
    Join Date
    Jul 2004
    Posts
    102
    Flyboy thanks for your replay.

    1 select months_between(sysdate,doj) from enw
    2* where pid like '%0712%'
    SQL> /

    MONTHS_BETWEEN(SYSDATE,DOJ)
    ---------------------------
    158.33526

    SQL> ed
    Wrote file afiedt.buf

    1 select months_between(sysdate,doj)/12 from enw
    2* where pid like '%0712%'
    SQL> /

    MONTHS_BETWEEN(SYSDATE,DOJ)/12
    ------------------------------
    13.194606
    What i don't understand is How i define the months of the above query.
    Working Together...

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Momin View Post
    What i don't understand is How i define the months of the above query.
    Still do not know what you do not understand. Probably how to interpret this? Well, the difference between those two days is 158.33526 months (158 months plus 10 days in 30-day month), so it is (slightly) over 13 years. Number of remaining (months) would be counted as reminder (MOD function) of this division - it will be 2.33526 months (again with those 10 days). If you do not want result in decimal figures, just get rid of that decimal part (TRUNC or FLOOR function).
    Still curious whether you are at least able to verify it.

  11. #11
    Join Date
    Jul 2004
    Posts
    102
    Please..............

    Please figure out the tenure ( ......years ....month) from my above example. with your code. Still I'm not able to code it.

    Please...please...
    Working Together...

  12. #12
    Join Date
    Dec 2010
    Posts
    7

    Very simple

    SELECT LAST_DAY(TO_CHAR(ADD_MONTHS('01-OCT-2009',13),'DD-MON-YYYY')) FROM DUAL

    try it, hope it will solve ur problem.

  13. #13
    Join Date
    Jul 2004
    Posts
    102
    Quote Originally Posted by sanjsajjan View Post
    SELECT LAST_DAY(TO_CHAR(ADD_MONTHS('01-OCT-2009',13),'DD-MON-YYYY')) FROM DUAL

    try it, hope it will solve ur problem.
    Your query returns last day of a certain month. But i need the tenure.
    Working Together...

  14. #14
    Join Date
    Dec 2010
    Posts
    7
    What is mean by tenure?

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select floor(months_between(sysdate,doj)/12) years,
    floor(mod(months_between(sysdate,doj),12)) months from enw
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •