php - mysql查询获取2个日期之间的第一个特定日期

Question

如何获得两个日期之间的第一个星期日？ 我的表中只有 2 个字段(dt_from 和 dt_to)

dt_from = '2016-08-6';
dt_to = '2016-08-19';

SELECT firstsunday FROM myTable BETWEEN dt_from AND dt_to;

Answer 1

获取日期本身之后的第一个星期日怎么样？

SELECT DATE_ADD(A.date_from, INTERVAL (6 - WEEKDAY(A.date_from)) DAY) from myTable as A;

http://sqlfiddle.com/#!9/7fce5

如果星期日不在日期之间:

SELECT DATE_ADD(A.date_from, INTERVAL (6 - WEEKDAY(A.date_from)) DAY) as firstsunday from myTable as A where DATE_ADD(A.date_from, INTERVAL (6 - WEEKDAY(A.date_from)) DAY) between A.date_from and A.date_to;

http://sqlfiddle.com/#!9/83d0f0/4

稍微短一点:

SELECT firstsunday from (SELECT A.date_from, A.date_to, DATE_ADD(A.date_from, INTERVAL (6 - WEEKDAY(A.date_from)) DAY) as firstsunday from myTable as A) as B where B.firstsunday between B.date_from and B.date_to;

http://sqlfiddle.com/#!9/6adab3/1