如何通过SQL查询计算两个日期之间的周数与天数？

刚好做过类似的需求！要得到像"1 week and 2 days"这样的格式化结果，我们可以通过拆分总天数差来实现，同时还要处理单复数和空值的情况，避免出现"0 weeks and 0 days"这种奇怪的输出。

核心思路 #

1. 先计算两个日期之间的总天数差
2. 用整除运算得到完整的周数（1周=7天）
3. 用取余运算得到剩余的天数
4. 通过条件判断拼接成符合语法的字符串（单数用week/day，复数用weeks/days，0值的部分直接忽略）

MySQL 示例代码 #

假设我们要计算指定的两个日期（比如2018-01-10到2018-01-19），可以用下面的SQL：

-- 定义起始和结束日期
SET @start_date = '2018-01-10';
SET @end_date = '2018-01-19';

SELECT 
    -- 用CONCAT_WS拼接，自动忽略空字符串
    CONCAT_WS(' ',
        -- 处理周数的单复数和空值
        CASE 
            WHEN weeks = 0 THEN ''
            WHEN weeks = 1 THEN CONCAT(weeks, ' week')
            ELSE CONCAT(weeks, ' weeks')
        END,
        -- 处理天数的单复数和空值，注意前面加"and "
        CASE 
            WHEN days = 0 THEN ''
            WHEN days = 1 THEN CONCAT('and ', days, ' day')
            ELSE CONCAT('and ', days, ' days')
        END
    ) AS duration
FROM (
    -- 内层查询计算周数和剩余天数
    SELECT 
        FLOOR(DATEDIFF(@end_date, @start_date) / 7) AS weeks,
        DATEDIFF(@end_date, @start_date) % 7 AS days
) AS calc;

运行这段代码就会返回你想要的结果：1 week and 2 days

关键部分解释 #

• DATEDIFF(@end_date, @start_date)：返回两个日期之间的总天数差（结束日期减起始日期）
• FLOOR(总天数/7)：向下取整得到完整的周数（比如9天就是1周）
• 总天数%7：取余得到剩余的天数（9天的话就是2天）
• CONCAT_WS(' ', ...)：用空格拼接多个字符串，自动跳过空值，避免出现多余的空格
• CASE语句：处理单复数，比如1周显示1 week，2周显示2 weeks；如果周数为0，就不显示周的部分

边界情况处理 #

• 当两个日期相同：返回空字符串（如果需要可以加个CASE判断总天数为0时返回"0 days"）
• 只有周数没有天数：比如2018-01-10到2018-01-17，返回1 week
• 只有天数没有周数：比如2018-01-10到2018-01-12，返回2 days

其他数据库的调整 #

如果用的是其他数据库，只需要调整计算总天数差的函数即可：

• SQL Server：把DATEDIFF(@end_date, @start_date)换成DATEDIFF(day, @start_date, @end_date)，变量声明用DECLARE @start_date DATE = '2018-01-10';
• Oracle：总天数差用TRUNC(end_date) - TRUNC(start_date)，变量可以用绑定变量或者DEFINE命令

表查询示例 #

如果要从表中批量计算每一行的日期差，直接把变量换成表字段即可：

SELECT 
    start_date,
    end_date,
    CONCAT_WS(' ',
        CASE 
            WHEN weeks = 0 THEN ''
            WHEN weeks = 1 THEN CONCAT(weeks, ' week')
            ELSE CONCAT(weeks, ' weeks')
        END,
        CASE 
            WHEN days = 0 THEN ''
            WHEN days = 1 THEN CONCAT('and ', days, ' day')
            ELSE CONCAT('and ', days, ' days')
        END
    ) AS duration
FROM (
    SELECT 
        start_date,
        end_date,
        FLOOR(DATEDIFF(end_date, start_date) / 7) AS weeks,
        DATEDIFF(end_date, start_date) % 7 AS days
    FROM your_table
) AS calc;

内容的提问来源于stack exchange，提问作者naveed ahmed