# Thread: Find the tenure Of a Employee

1. Registered User
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.

2. Registered User
Join Date
Mar 2007
Posts
627
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. Registered User
Join Date
Jul 2004
Posts
102

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 04:46.

4. Registered User
Join Date
Mar 2007
Posts
627
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. Registered User
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 ....

6. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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```

7. Registered User
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.

8. Registered User
Join Date
Mar 2007
Posts
627
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. Registered User
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.

10. Registered User
Join Date
Mar 2007
Posts
627
Originally Posted by Momin
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. Registered User
Join Date
Jul 2004
Posts
102

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

12. Registered User
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. Registered User
Join Date
Jul 2004
Posts
102
Originally Posted by sanjsajjan
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.

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

15. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
select floor(months_between(sysdate,doj)/12) years,
floor(mod(months_between(sysdate,doj),12)) months from enw

#### Posting Permissions

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