如何确定两个日期之间相隔了多少个月、周和天？

Question

INFORMIX-SQL 7.32 (SE) 执行屏幕：

假设我的开始日期为 2010 年 2 月 15 日，结束日期为 2010 年 5 月 27 日。我可以使用“let elapsed_days = end_date - start_date”计算经过的天数，但如何将这些天数转换为 3 个月、1 周和 5 天？

我见过使用的原始计算方法，每个月四舍五入为 31 天，因为如果您将每个月的天数相加并除以 12，则平均天数为 30.5 天 每月，然后将经过的天数除以 31 得到 3.31 个月，但这种方法对于我的需求来说是不可接受的。

Answer 1

这可能可以经受一些更严格的测试，并且肯定有空间整理输出（即删除“0 个月”子字符串），但我认为它可以让您大部分顺利完成......

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;

即：

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