Fraction of Month

I need to get "Tenure" of employees based on startDate and EndDate

In my attached select it returns whole months.

start  2011-06-27 and end 2011-12-14 would be 5 whole months but 6 is returned
start  2008-06-12 and end 2009-03-26 correctly returns 9 months

Little help?
Select @temure = datediff(mm,u.employmentStart,IsNull(u.employmentEnd,getdate()))

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
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.

s___kCommented:
Try this:
@tenure=select datediff(m, u.employmentStar, u.employmentEnd) -
case when datepart(d, u.employmentStar) > datepart(d, u.employmentEnd) then 1 else 0
end
0
Patrick MatthewsCommented:
The reason for this is that datediff (in SQL Server, VBA, and Access) does NOT calculate elapsed time.  Rather, it returns how many time period boundaries there are between two dates/times.

For example:

DATEDIFF(year, '2011-01-01', '2012-12-31 23:59:59')
DATEDIFF(year, '2011-12-31 23:59:59', '2012-01-01')

both return the same value, 1 (year), even though in the first case very nealy two years have elapsed, and in the second case only one second has elapsed.  That said, for both cases, a single year boundary is crossed when moving from the start value to the end value.

When the time interval is seconds or milliseconds, you may as well treat the result as "elapsed time", but for any interval longer than that, remember this warning :)
0
s___kCommented:
Sorry for the incorrect syntax :)
set @tenure=(select datediff(m, u.employmentStar, u.employmentEnd) - case when datepart(d, u.employmentStar) > datepart(d, u.employmentEnd) then 1 else 0 end)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

sureshbabukrishCommented:
best way to do this, get the difference in seconds then convert them into months
0
kinjalinCommented:
As per my understanding please try the below

Select case when DAY('2011-06-27') > Day('2011-12-14') then datediff(mm,'2011-06-27',IsNull('2011-12-14',getdate()))-1 else
datediff(mm,'2011-06-27',IsNull('2011-12-14',getdate())) end

0
Larry Bristersr. DeveloperAuthor Commented:
Worked.  Thanks
0
Patrick MatthewsCommented:
sureshbabukrish,

>>best way to do this, get the difference in seconds then convert them into months

That is not going to work, seeing as how months have differing numbers of days.

Patrick
0
Larry Bristersr. DeveloperAuthor Commented:
That's why I went with s___k: answer.

Loaded and actually looked at the data for about 100 instances in a 10000 row table and they were all correct.

If anything wierd shows up I'll handle at that time
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.