1

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.

  • Omrakhur, Did my solution work ? – Prabhat G Jun 14 '17 at 8:50
  • What is the answer for Jan 31, 2016 to Mar 1, 2016? – Rick James Jun 28 '17 at 15:39
  • And this one is a worse case; it wrecks all the algorithms I have thought of: Jan 31, 2016 to Mar 30, 2016 – Rick James Jun 29 '17 at 22:22
  • @RickJames Neither of the answers are working. Which one are you referring to in the last comment above? – omrakhur Jun 30 '17 at 10:23
  • I was thinking about shifting by 1 month if the day-of-month was less/greater, then computing the years/months. But then got stumped on computing the days. Give me the answer for those date ranges, and maybe I will try again. – Rick James Jun 30 '17 at 16:34
0

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;
0

try this :

Select
TIMESTAMPDIFF( YEAR, startdate, now() ) as _year
,TIMESTAMPDIFF( MONTH, startdate, now() ) % 12 as _month
,FLOOR( TIMESTAMPDIFF( DAY, startdate, now() ) % 30.4375 ) as _day
FROM users

I tried it as, which gave me correct result:

Select
TIMESTAMPDIFF( YEAR, '2016-02-10', now() ) as _year
,TIMESTAMPDIFF( MONTH, '2016-02-10', now() ) % 12 as _month
,FLOOR( TIMESTAMPDIFF( DAY, '2016-02-10', now() ) % 30.4375 ) as _day
0

This is what I use for calculating the duration.

SELECT CONCAT(TIMESTAMPDIFF(YEAR, '2016-02-10', CURDATE()), ' Yrs ',
TIMESTAMPDIFF(MONTH, '2016-02-10', CURDATE())%12, ' Mth ',
TIMESTAMPDIFF(DAY, ('2016-02-10' + INTERVAL TIMESTAMPDIFF(YEAR, '2016-02-10', 
CURDATE()) YEAR
+ INTERVAL TIMESTAMPDIFF(MONTH, '2016-02-10', CURDATE())%12 MONTH), CURDATE()), ' 
Days') AS `Duration`

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.