# 5 years before ORIG_START_DATE

Hi

I know this is pretty simple but this isnt working for me for some reason.

All i want to do is return staff who have been at the company 5 years from their ORIG_START_DATE. I only want this to return people who have been working for 5 years in the month they have been working 5 years.

``````  AND ADD_MONTHS(D550M.ORIG_START_DATE,5*12)BETWEEN sysdate AND sysdate+31

``````

What am i missing?

Thanks
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A space between ) and BETWEEN, for one thing....
where ORIG_START_DATE between trunc (sysdate,'MM') - 5*365-1 and trunc(last_day(sysdate)) - 5*365-1
I may be overlooking a correct source, but I don't find any examples allowing the num_months to be calculated.  Try simply, "60".
Commented:
Author Commented:
Morning all - thanks for the prompt replies.

I tried this:

``````AND D550M.ORIG_START_DATE >= add_months(trunc(sysdate,'mm'),-60)
``````

and

``````ORIG_START_DATE between trunc (sysdate,'MM') - 5*365-1 and trunc(last_day(sysdate)) - 5*365-1
``````

but neither worked.

The original start date of the employee is 5 years ago today - 12/05/2010.

any ideas?
Author Commented:
This worked!

ORIG_START_DATE <= sysdate - interval '5' year
strange, the <= in what you say is working would mean that employees that are working longer than 5 years would also be in your result. from what I understood this is not what you asked for?
Author Commented:
I used this:

ORIG_START_DATE >= sysdate - interval '5' year
and
ORIG_START_DATE <= sysdate - interval '6' year
Commented:
that's not what you asked for though,  that will give you people that have worked for 5 years for an entire year, not for the month they started.

note  I used ADD_MONTHS with -60 intentionally  instead of interval '5' year.

the reason for that choice is interval '5' year won't work on Feb 29  but add_months will.

also I used < instead of <=  for the upper boundary intentionally as well so you don't include one extra day.

if this didn't work:

``````AND D550M.ORIG_START_DATE >= add_months(trunc(sysdate,'mm'),-60)
``````

please explain what it did or did not do that you were expecting.
it does what you asked, but maybe not what you intended.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
I'm out of the office now.

Thus will run on the first of each month, I understand your logic but it didn't find the employees I was looking for.

Your logic seems better, but ad this is run on he first of each month I think it will be ok.

I'll try your example tomorrow and let you kno how I get on.  Thanks alot.

Rich
Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for Gerwin Jansen's comment #a40772195
Assisted answer: 0 points for Richiep86's comment #a40772227
Assisted answer: 250 points for sdstuber's comment #a40772255

for the following reason:

This helped me find the solution i was looking for! Thanks
Author Commented:
Help me find me solution
Commented:
I got all of the points.  Did you want to do a split or did you change your mind?

we can reopen the question and close again to split with Gerwin Jansen's post if you want
I'm seeing a split? It's OK with me ;)
Author Commented:
Do I need to do anything lads?
Thanks to all that helped.
Very grateful.
Commented:
hmmm I don't see a split.

I see a comment where a split was intended,  but then the actual close is all for me.