Oracle SQL: Finding Difference Between two dates in YEARS


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:

SQL> SELECT last_name, hire_date, 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!

About these ads

About Anatoly Spektor

My name is Anatoly Spektor (originally Anatolijs Spektors) I am Software and Web Developer. I have worked in Seneca Center for Development of Open Technology on Big Blue Button Add-on - Polling Module. Currently, I am employed as Software Engineer in Red Hat.
This entry was posted in Database, ORACLE, SQL and tagged , , , , , , . Bookmark the permalink.

5 Responses to Oracle SQL: Finding Difference Between two dates in YEARS

  1. 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. 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 says:

    THANK YOU!

  4. Nickolas says:

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

  5. Earnest says:

    I read a lot of interesting posts here. Probably
    you spend a lot of time writing, i know how to save you a lot of
    work, there is an online tool that creates readable,
    SEO friendly articles in seconds, just search in google –
    laranitas free content source

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s