Displaying employee stats from a table

I have a table emp which has many fields, one of them is emp.hiredate. So I created a datablock, which displays the fields of the emp table and I added a display item field at the end. I need to display (how long it's been for the employee since he/she started) in years, months and days... How can I do that? I am using Oracle 6/6i.

    Requires Free Membership to View

    Login

It's a simple expression to calculate your answer. You could use the MONTHS_BETWEEN function to determine the number of months between today's date (sysdate) and the hiredate. Or, you could simply subtract the hiredate from sysdate and calculate each part of the answer in steps (divide the # days returned by 365 to get years and so on).

There could be many, many ways to do it. Here's just one example: 

select trunc((sysdate - to_date('01/14/2001','mm/dd/yyyy')) / 365,0) as years,
       trunc(mod((sysdate - to_date('01/14/2001','mm/dd/yyyy')), 365) / 31 , 0) as months,
       trunc(mod((sysdate - to_date('01/14/2001','mm/dd/yyyy')), 365),0) - 31 as days
  from dual
/

This was first published in March 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top