Current Year minus Start Year

Transact-SQL
1

Hi,

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

Thanks

2

SELECT Employee_Name, Employee_Start_Year, (YEAR(GETDATE()) - Employee_Start_Year) as Service_Length
FROM Employees

Note: the 'as Service_Length' is creating an alias for your column. You don't necessarily have to specify a column name

3

Thank you! I almost had this but think I missed out some of the brackets so it wasn't quite working.

Thanks again.

4

In addition, are you able to kindly advise how I could return 'N/A" as Service_Length if the Employee_Start_Year is 0?

Thank you!

5

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

6

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.