# 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()))
``````
###### 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.

Commented:
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
Commented:
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 :)
Commented:
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)

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.

Commented:
best way to do this, get the difference in seconds then convert them into months
Commented:
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

sr. DeveloperAuthor Commented:
Worked.  Thanks
Commented:
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
sr. 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
###### 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.