title: Calculate years in service
body: <p>I made the following sql query to get a list of all employees who are 10 years in service. I'm wondering if this is the best and most efficient way to do this. For example: how can I calculate a list of all employees who are 12.5 in service?</p>
  
  <pre><code>DECLARE     @YEAR_CURRENT VARCHAR(4)
  SET         @YEAR_CURRENT = Year(getdate())
  
  SELECT 
      DISTINCT 
      fullname, 
      CONVERT(varchar(10),CONVERT(date,DATEADD(year,10,years_in_service)),105) as 'anniversary', 
      ROUND((Datediff(dd,years_in_service,isnull(@YEAR_CURRENT,getdate()))/CONVERT(FLOAT, 365)),1) AS years_in_service_today
  FROM my_database
  WHERE
      AND ROUND((Datediff(dd,years_in_service,isnull( @YEAR_CURRENT,getdate()))/CONVERT(FLOAT, 365)),1) &gt;= 10
      AND ROUND((Datediff(dd,years_in_service,isnull( @YEAR_CURRENT,getdate()))/CONVERT(FLOAT, 365)),1) &lt; 11
  ORDER BY 
      fullname ASC
  </code></pre>