[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL - SUBTRACT DATES GET YEARS?

Posted on 2014-04-18
4
Medium Priority
?
721 Views
Last Modified: 2014-04-18
I'm trying to figure out a query function in a Oracle SQL database.

I'm trying to have the query generate the number of years between two dates.

This is my query so far:

select	 "EMPLOYEE"."EMP_NUM" as "EMP_NUM",
	 "EMPLOYEE"."EMP_LNAME" as "EMP_LNAME",
	 "EMPLOYEE"."EMP_FNAME" as "EMP_FNAME",
	 "EMPLOYEE"."EMP_HIREDATE" as "EMP_HIREDATE",
	   TO_CHAR (SYSDATE, 'DD-MON-YY') as "QUERY_DATE"
 from	 "EMPLOYEE" "EMPLOYEE"

Open in new window


I need a column between EMP_HIREDATE and QUERY_DATE called YEARS_EMPLOYED. Like so:
years
It will state for example "11" in the row if the employee has been hired that long.

I'm really at a lost on how to do this. I imagine it obvious deals with subtracting the two dates, but I'm not quite sure where to start. I imagine that maybe I have to subtract the two dates, then divide by 365? But what commands would be used for that, that will work with the above script anyways that I have so far..?
0
Comment
Question by:Pancake_Effect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009788
Do you want to round up or down?

Check out MONTHS_BETWEEN(sysdate, EMP_HIREDATE)

sysdate is the current OS date.

That gives you MONTHS.  Then all you need to do is divide by 12:

 MONTHS_BETWEEN(sysdate, EMP_HIREDATE) / 12

That gives you fractional results.

Then you can FLOOR, CEIL or ROUND the result.

The online docs tell you what all those functions do:
docs.oracle.com/cd/E11882_01/server.112/e41084/functions067.htm
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40009791
Get in the habit of not using quotes.  It forces case sensitivity in Oracle.  I realize some tools add them for you but if you are writing your own, don't use them.

Also use table aliasing.  You do but you aliased the employee table as employee.

Also for single tables, you don't need it.

See if this works:
select	 E.EMP_NUM,
	 E.EMP_LNAME,
	 E.EMP_FNAME,
	 E.EMP_HIREDATE,
	   TO_CHAR (SYSDATE, 'DD-MON-YY') QUERY_DATE,
	 floor(months_between(sysdate, emp_hiredate)/12) Years_worked
 from	 EMPLOYEE E;

Open in new window

0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009799
Thanks! I was was very close, I was just trying to minus two other fields, instead of simply the SYSDATE and the other time. Didn't know about that Floor or Round function though.

Works perfect, and thank you for that link. That's going to be very helpful.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009805
Glad to help.

The online docs will become your best friend!

At times I still scan the SQL reference and read about functions that I don't recognize and I've been using Oracle for over 20 years.  Every new release provides new ones and I never knew all the old ones!

As you pointed out, subtracting two dates gives you 'days' but dividing by 365 can lead to inaccurate results.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question