MySQL – query to get length of service in Years, months, days

datetimeMySQL

I have the following query that I'm trying to get the length of service of a person at their company. The idea is to find how many years, months, and days they have served there.

For example:

Todays date: Jun 13, 2017.

Exhibit A: John Doe has served since Feb 10, 2016. So length of service should be 1 year, 4 months, 3 days excluding the end date.

Exhibit B: Jane Doe has served since 13th Jun, 2007. So length of service should be Or 10 years excluding the end date.

My Query spoiler: it's not right

    SELECT  `firstname` ,
            DATE_FORMAT( CURDATE( ) ,  '%Y' ) - DATE_FORMAT( startdate,  '%Y' ) - ( DATE_FORMAT( CURDATE( ) ,  '00-%m-%d' ) < DATE_FORMAT( startdate,  '00-%m-%d' ) ) AS years, 
            PERIOD_DIFF( DATE_FORMAT( CURDATE( ) ,  '%Y%m' ) , DATE_FORMAT( startdate, '%Y%m' ) ) AS months, 
            DATEDIFF( CURDATE( ) , startdate ) AS days
    FROM users

It gives me the results in years, as well as months, as well as days. So for someone who has served a year it would output: 1 year, 12 months, 365 days etc.

Best Answer

A possible (not the best) solution to this:

SELECT firstname
     , startdate
     , CASE
           WHEN YEAR(CURDATE()) < YEAR(startdate) THEN 0 #or some such validation
           ELSE YEAR(CURDATE()) - YEAR(startdate)
       END years
     , CASE
           WHEN MONTH(CURDATE()) < MONTH(startdate) THEN MONTH(startdate) - MONTH(CURDATE())
           ELSE MONTH(CURDATE()) - MONTH(startdate)
       END months
     , CASE
           WHEN DAY(CURDATE()) < DAY(startdate) THEN DAY(startdate) - DAY(CURDATE())
           ELSE DAY(CURDATE()) - DAY(startdate)
       END days
FROM users;
Related Question