Help with Date functions

[tkaz]

I am using SQL Server 2008.

I'm trying to write a fairly simple query that got complicated when asked to include the Years of Service based off the last day of the year.

This is the query I have:

SELECT Employee,LastName,FirstName,PRGroup,PRDept,EarnCode, CONVERT(CHAR(10), HireDate, 101) AS HireDate,DATEDIFF(year,HireDate,'2013') AS YOS
from PREH
where PRCo = '200'
and ActiveYN = 'Y'
order by YOS

This works great except for two things...I want to be able to allow the user to enter the last day of the year through a parameter and it only returns whole years and management wants years and months. For example: Charlie Justice is hired on 4/19/2006 so he will have 7.70 Years of Service on 12/31/2013.

I realize that I'm missing something, but date functions have NEVER, EVER been my strong suit and I'm stuck. Can someone please help because I have a deadline and I'm just shut down on this one.

The output for my query is:

Employee LastName FirstName PRGroup Pr Dept EarnCode HireDate YOS
1111 Justice Charlie 45 1000 130 04/19/2006 7

I want it to be:

Employee LastName FirstName PRGroup Pr Dept EarnCode HireDate YOS
1111 Justice Charlie 45 1000 130 04/19/2006 7.70

Thanks in advance for helping me out.

 

[gmmastros]

I'm not near a computer right now, but you could try doing a date diff on days and then divide by 365.25. Make sure you include the decimal point, or you will get integer math.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tkaz]

Thank you so much! That got me where I needed to be. Maybe I'll finish this query on time after all. Only a couple more little caveats to worry about

SELECT Employee,LastName,FirstName,PRGroup,PRDept,EarnCode, CONVERT(CHAR(10), HireDate, 101) AS HireDate,udRehireDate,CONVERT(Decimal(5,2),(DATEDIFF(Day,HireDate,'12/31/2013')/365.25)) as YOS
from PREH
where PRCo = '200'
and ActiveYN = 'Y'
order by HireDate