Problem solve Get help with specific problems with your technologies, process and projects.

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

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.