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
This was first published in March 2004