How can I determine how many months, weeks and days have elapsed between two dates?

Question

INFORMIX-SQL 7.32 (SE) Perform screen:

Let's say I have a start date of FEB-15-2010 and an end date of MAY-27-2010. I can calculate the number of elapsed days with 'let elapsed_days = end_date - start_date', but how can I convert these number of days into 3 months, 1 week and 5 days?

A raw calculation I've seen used, rounding every month to 31 days, since if you take the number of days in each month, add them up and divide them by 12 gives you 30.5 days average per month, then taking elapsed days and dividing it by 31 produces 3.31 months, but this method is unacceptable for my needs.

Answer 1

This could probably stand some more rigorous testing, and there is certainly scope to tidy up the output (ie remove "0 months" substrings), but I think it gets you most of the way there...

CREATE PROCEDURE informix.datediff(d1 DATE, d2 DATE) RETURNING VARCHAR(255);
    DEFINE yrcount, mthcount, wkcount, daycount INTEGER;
    DEFINE dx DATE;

    LET mthcount = ((YEAR(d2) - YEAR(d1)) * 12) + MONTH(d2) - MONTH(d1);
    IF DAY(d1) <= DAY(d2) THEN
        LET daycount = DAY(d2) - DAY(d1);
    ELSE
        LET dx = MDY(MONTH(d1),1,YEAR(d1))+1 UNITS MONTH;
        LET daycount = dx - d1;     -- elapsed days from last month
        LET daycount = daycount + DAY(d2) - 1; -- elapsed days from this month
    END IF;

    LET yrcount = mthcount / 12;
    LET mthcount = MOD(mthcount,12);
    LET wkcount = daycount / 7;
    LET daycount = MOD(daycount,7);

    RETURN d1 || " - " || d2 || ": " || yrcount || " years, " || mthcount
         || " months, " || wkcount || " weeks and " || daycount || " days ";
END PROCEDURE;

ie:

execute procedure datediff(TODAY, "19/03/2011");
(expression)  21/06/2010 - 19/03/2011: 0 years, 9 months, 4 weeks and 0 days

execute procedure datediff(TODAY, "22/03/2011");
(expression)  21/06/2010 - 22/03/2011: 0 years, 9 months, 0 weeks and 1 days

execute procedure datediff("08/02/2010", "08/05/2011");
(expression)  08/02/2010 - 08/05/2011: 1 years, 3 months, 0 weeks and 0 days

execute procedure datediff("31/03/2010", TODAY);
(expression)  31/03/2010 - 21/06/2010: 0 years, 3 months, 3 weeks and 0 days

execute procedure datediff(TODAY-3, TODAY);
(expression)  18/06/2010 - 21/06/2010: 0 years, 0 months, 0 weeks and 3 days

execute procedure datediff(TODAY-33, TODAY);
(expression)  19/05/2010 - 21/06/2010: 0 years, 1 months, 0 weeks and 2 days