MySQL:如何计算从某个特定日期开始的周数?

使用 DATEDIFF function :

ROUND(DATEDIFF(end_date, start_date)/7, 0) AS weeksout

这个 0 是要在 ROUND

有一个简单的方法:

SELECT EventDate, (week(EventDate) - week(curdate())) AS WeeksOut FROM Events;

例子:

mysql> select week('2010-11-18') - week ('2010-10-18');
+------------------------------------------+
| week('2010-11-18') - week ('2010-10-18') |
+------------------------------------------+
|                                        4 |
+------------------------------------------+
1 row in set (0.00 sec)

另一种选择是计算间隔天数并除以7:

SELECT EventDate, datediff(EventDate,curdate())/7 AS WeeksOut FROM Events;

例子:

mysql> select datediff('2010-11-18' , '2010-10-18') / 7;
+-------------------------------------------+
| datediff('2010-11-18' , '2010-10-18') / 7 |
+-------------------------------------------+
|                                    4.4286 |
+-------------------------------------------+
1 row in set (0.00 sec)

WEEK() ,我发现下面的方法相当有效。

SELECT 
    YEAR(end_date)*52+WEEK(end_date)
    - YEAR(start_date)*52 - WEEK(start_date) as weeks_out
FROM
    events;

这种方法的区别(与 DATEDIFF 方法)是它与周对齐。所以今天(星期一)和上星期五会回来 1 使用此方法,但将返回 0 与 日期差异