如何在日期范围内生成周结束日期(星期六)?

Question

我需要在查询或临时表中生成列(不确定需要哪一个)

，这样我就可以有一个星期六的日期列表，在给定的日期范围内。

此列表将在连接中用于将记录与周关联。

我有什么选择?

样本输入:

: 03/01/2013

: 04/30/2013

结果:

星期结束——03/02/2013——03/09/2013——03/16/2013——03/23/2013——03/30/2013——04/06/2013——04/13/2013——04/20/2013——04/27/2013——05/04/2013

当前代码:

create table #TBL7(YEAR  INT, WEEKNUMBER  INT, STARTDATE  DATETIME, ENDDATE DATETIME)

begin
    declare @startdate datetime
                    , @enddate datetime
                    , @ctr int
    SET @startdate = CAST(2013 AS VARCHAR)+ '/01/01'
    SET @enddate = CAST(2013 AS VARCHAR) + '/12/31'
    SET @ctr = 0
    WHILE @enddate >= @startdate
    BEGIN
            SET @ctr = @ctr + 1
            INSERT INTO #TBL7
            values(year(@startdate), @ctr, @startdate, @startdate + 6)
            SET @startdate = @startdate + 7
    END
end

select * from #TBL7

Answer 1

首先创建一个日历表。然后你有一个非常简单的查询:

select [Date] 
from dbo.Calendar 
where DayOfWeek = 'Saturday' and [Date] between '20130301' and '20130430'

日历表几乎总是处理日期的最佳方法，因为你是在处理数据，而不是代码，所以你可以看到它是正确的，没有需要维护的神秘代码。

Answer 2

这是Oracle代码。对不起，我不知道如何将此转换为SQL SERVER。应该不会很难。你所需要的只是使用合适的函数来代替to_date()和to_char()，并计算开始和结束日期之间的差异，例如(end_date-start_date)+1:

WITH data(r, some_date) AS 
(
 SELECT 1 r, to_date('03/01/2013', 'MM/DD/YYYY') some_date FROM dual
  UNION ALL
 SELECT r+1, to_date('03/01/2013', 'MM/DD/YYYY')+r FROM data WHERE r < 61 -- (end_date-start_date)+1
)
 SELECT some_date
      , To_Char(some_date, 'DY') wk_day
   FROM data
  WHERE To_Char(some_date, 'DY') = 'SAT'
  /
SOME_DATE    WK_DAY
--------------------
3/2/2013     SAT
3/9/2013     SAT
3/16/2013    SAT
3/23/2013    SAT
3/30/2013    SAT
4/6/2013     SAT
4/13/2013    SAT
4/20/2013    SAT
4/27/2013    SAT

Answer 3

应该可以:

WITH cteWeeks (WeekEnding) As
(
   -- Find the Saturday of the first week.
   -- Need to allow for different DATEFIRST settings:
   SELECT
      CASE
         WHEN DatePart(dw, DateAdd(day, @@datefirst, @StartDate)) = 7 THEN @StartDate
         ELSE DateAdd(day, 7 - DatePart(dw, DateAdd(day, @@datefirst, @StartDate)), @StartDate)
      END
   UNION ALL
   SELECT
      DateAdd(day, 7, WeekEnding)
   FROM
      cteWeeks
   WHERE
      WeekEnding < @EndDate
)
SELECT
   WeekEnding
FROM
   cteWeeks
;

http://www.sqlfiddle.com/!3/d41d8/12095