Q

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. 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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close