Displaying datediff in years to 2 decmal places.

I am trying to calculate the length of service for emloyees in years to 2 decimal places.

Here is my sql statement:

Case when coalesce(EL.TermDate,0) =0 then convert(decimal(15,2),round((DATEDIFF(mm,EL.DOH,getdate())/12),2)) else convert(decimal(15,2),round(DATEDIFF(mm,EL.DOH, EL.TermDate)/12,2)) end

The results show the # of years but rounded to Zero places.

If an employee has worked 18 months, then the result s/b 1.50.

How can fix this?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

RayData AnalystCommented:
I'd do this (didn't check precise syntax, only suggesting the concept:
getting days difference and dividing by 365.0 does two things.
1) it turns your results into a decimal number
2) turns your decimal days into a year
The cast turns years into a 2 digit decimal (in using 8,2 I've allowed for a maximum of 999999.99 years)

Case when coalesce(EL.TermDate,0) =0 then
cast ( DATEDIFF(d,EL.DOH,getdate()) / 365.0) as decimal (8,2) )  else
cast ( DATEDIFF(d,EL.DOH, EL.TermDate) /365.0 as decimal (8,2) )  end

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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
It is important to either convert the date diff to float prior to dividing, or dividing by a float. Otherwise you are dividing int/int, and that is again int. So in your original query just use 12.0 instead of 12.
RayData AnalystCommented:
Qlemo, the only issue I see with your answer is that without a cast there will be no control over the number of decimal places in the result.  I do like the simple answer though of changing the 12.0 if not for the loss of 'detail' on using months vs. days.  Admittedly, I don't care for things being rounded 'too much' so this is just my 'pickyness'     :-)
GPSPOWAuthor Commented:
Thank you

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.