Ask the Expert

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

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: