Find Difference Between two dates in YEARS with Oracle

My friend asked me how to create following SQL query:

For each employee hired before 1992, display the employee’s last name, hire date and calculate the number of YEARS between TODAY and the date the employee was hired. Label the column Years worked.
Order your results by the number of years employed.
Round the number of years up to the closest whole number.

I was trying to find the solution in the Net, but unfortunately found nothing that would help, that is why I decided to make this query by my one. It appeared to be very simple.

Here is the query:

TO_CHAR(ROUND((sysdate – hire_date)/365)) "Years Worked"
2 FROM EMPLOYEES
3 WHERE hire_date < ‘1-Jan-1992’
4 ORDER BY "Years Worked" desc;

So first line selects employee last_name and hire_date, and also I created new column called Years Worked by getting the Date in number format (TO_CHAR) subtracting hire_date from the system date and dividing by 365 to find the value in year. If the year has 366 days it does not really matter because we are using ROUND function and it cuts all decimals. In line 3 WHERE clause checks if hire_Date is smaller than 1992.Last line orders everything by Years Worked column starting from the biggest amount of years worked.

That’s it, Have fun!

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Anatoly Spektor

IT Consultant with 6 years experience in Software Development and IT Leadership. Participated in such projects as Eclipse IDE, Big Blue Button, Toronto 2015 Panam Games.

Related Posts

Join the Discussion

Your email address will not be published. Required fields are marked *

Discussion

  1. Suansh Singhal

    Oracle support Mathematical Subtract ‘-‘ operator on Data datatype. You may directly put in select clause following statement:

    to_char (s.last_upd – s.created, ‘999999D99’)

    Further divide the result by 365 to calculate difference in Years.

    For detailed example, you may refer a post available at – http://crackingsiebel.wordpress.com/2010/12/01/oracle-sql-date-difference-in-days/

  2. Anatoly Spektor

    Thank you for sharing your thoughts.

    However, even if the example you are showing works fine, It looks very confusing, and person who will work on it after you, will be very annoyed with such code.

    My code, on the other hand, is much more readable.

    Regards,

    Anatoly

  3. ROCKY

    THANK YOU!

  4. Nickolas

    Thank you very much ! It’s really easy readable and useful.

  5. Abeer A. Ahmed

    You rock, Anatoly ッ

  6. swati sharma

    The Round () function/utility is utilized to round a statist field to the digit of decimals defined.

    http://www.inoracle.com/sql-round-function-oracle/

  7. sree

    It helpful for me

  8. rekha

    could you please give an easy solution in oracle for this query
    find the length of service of the employees (of the form n years and m months).

  9. jacuzzi

    Find out the employee names and number of years worked along with their department names in descending order by number of years (up to two decimal points) worked.

  10. Tom

    You could also do something like this

    select extract(year from (sysdate – hire_date) year to month) “Years Worked”
    from employees
    where hire_date < to_date('1-Jan-1992', 'dd-Mon-yyyy')
    order by "Years Worked" desc;

arrow