Dev Nest Guide
sqlintermediate10 minutes

Create a SQL Function to Calculate Employee Tenure in Years

Write a SQL function that calculates the tenure of an employee in years based on their hire date and an optional end date. The function should return the number of full years the employee has worked.

Challenge prompt

Create a SQL function named CalculateTenure that takes two parameters: hire_date (DATE) and end_date (DATE, nullable). The function should return the number of full years the employee has worked. If end_date is NULL, use the current date as the end date. Calculate the tenure as full years, ignoring partial years.

Guidance

  • • Consider how to handle cases where end_date is NULL (use current date).
  • • Make sure to correctly calculate full years between dates, not just the difference in days.
  • • Use built-in date functions and conditionals in your SQL dialect to implement the logic.

Hints

  • • You can use the DATEDIFF or equivalent function but be careful to only count full years.
  • • Use CASE or IF statements to set the end date to current date when NULL.
  • • Try extracting the year, month, and day parts to compare and adjust for partial years.

Starter code

CREATE FUNCTION CalculateTenure(hire_date DATE, end_date DATE) RETURNS INT BEGIN
  -- Your code here
END;

Expected output

CalculateTenure('2015-06-15', '2020-06-14') returns 4 CalculateTenure('2015-06-15', '2020-06-15') returns 5 CalculateTenure('2015-06-15', NULL) returns tenure up to today

Core concepts

SQL functionsDate calculationsConditional logic

Challenge a Friend

Send this duel to someone else and see if they can solve it.