Pancake_Effect
asked on
SQL - SUBTRACT DATES GET YEARS?
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:
I need a column between EMP_HIREDATE and QUERY_DATE called YEARS_EMPLOYED. Like so:
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..?
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"
I need a column between EMP_HIREDATE and QUERY_DATE called YEARS_EMPLOYED. Like so:
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..?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Works perfect, and thank you for that link. That's going to be very helpful.
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.
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.
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_