I have a column that contains an employee start year e.g. 2012 and I am trying to add another column that will determine the length of service (in years) by doing the current year minus the Employee Start Year.
Do you know if this is possible please?
SELECT Employee_Name, Employee_Start_Year
FROM Employees
This can be done - but it will cause issues because you are returning different data types. The better alternative is to handle that in the report itself - or return 0 as a valid service length.
coalesce(nullif(cast(year(getdate()) - Employee_Start_Year as varchar(2)), '0'), 'N/A') As Service_Length
With that said - this is not going to truly show an employee's one year anniversary or one year of service. Suppose the employee was hired in December and you run this today - that employee has 2 months of service but will show up as one year.
You should be looking at the difference between the employees start date and today instead - and use the same calculation you would use for age.
datediff(year, employee_start_date, getdate()) - case when getdate() < dateadd(year, datediff(year, employee_start_date, getdate()), employee_start_date) then 1 else 0 end
Just looking at the start year rather than the specific date is perfect for what I need but thank you so much for the detailed solution, I really appreciate it.