存储过程列出两个日期之间的所有星期 [英] Stored procedure to list all weeks between two dates

问题描述

我搜索过，但找不到如何列出两个日期之间的星期的解决方案。

I've searched but could not find a solution on how to list the weeks inbetween two dates.

我找到了解决方案，列出了所有的日子两个日期之间的一个月：

I've found solutions that lists out all the days in the month between two dates:

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'

但是没有列出这几周为7天的格式，如下所示：

But nothing lists out the weeks as a 7 day week format, per below:

你该怎么做？

周开始：2015-02-01 - 周末：2015-02-07

Week Start: 2015-02-01 - Week End: 2015-02-07

周开始：2015-02-08 - 周末：2015-02-14

Week Start: 2015-02-08 - Week End: 2015-02-14

周开始： 2015-02-15 - 周末：2015-02-21

Week Start: 2015-02-15 - Week End: 2015-02-21

周开始：2015-02-22 - 周末：2015-02-28

Week Start: 2015-02-22 - Week End: 2015-02-28

推荐答案

您可以选择所有日子，并在一周的第一天进行过滤（ date_format（selected_date，'％w' ）= 0 ）：

You could select all days and filter on the first day of the week (date_format(selected_date, '%w') = 0):

select selected_date, date_add(selected_date, INTERVAL 6 DAY)
from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date 
 from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
  AND date_format(selected_date, '%w') = 0

SQLFIDDLE： http://sqlfiddle.com/#!2/7bc0e/52

SQLFIDDLE: http://sqlfiddle.com/#!2/7bc0e/52

您可以使用

AND DAYOFWEEK（selected_date）= 1

而不是

date_format（selected_date，'％w'）= 0

我认为是一点点可读性： http://sqlfiddle.com/#!2/7bc0e/54

I think it is a little bit more readable: http://sqlfiddle.com/#!2/7bc0e/54

这篇关于存储过程列出两个日期之间的所有星期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！