显示从开始日期到结束日期的周数

Question

我想给出开始日期和结束日期作为输入，我需要这些日期之间的周数列表。例如：如果我给起始日期为 01/11/2019 和结束日期为 14/12/2019，我的输出将是

1

2

3

4

5

1

2

3

（因为前 5 个是 11 月的几周，接下来的 3 个是 12 月的几周）......让我们看另一个例子：如果我将开始日期指定为 14/11/2019 并将结束日期指定为 14/12 /2019 我的输出将是

3

4

5

1

2

3

（因为前 3 个是 11 月的几周，接下来的 3 个是 12 月的几周） ....

DECLARE @StartDate AS DATETIME

DECLARE @EndDate AS DATETIME

DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2019-11-01'

SET @EndDate = '2019-12-14'

SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)

BEGIN

Print datepart(day, datediff(day, 0, @CurrentDate)/7 * 7)/7 + 1

SET @CurrentDate = DATEADD(DAY, 7, @CurrentDate); 

END

Answer 1

你可以试试下面的脚本 -DECLARE @StartDate AS DATETIMEDECLARE @EndDate AS DATETIMEDECLARE @CurrentDate AS DATETIMESET @StartDate = '2019-11-01'SET @EndDate = '2019-12-14'SET @CurrentDate = @StartDateWHILE (@CurrentDate < @EndDate)BEGINIF datepart(DD,@CurrentDate) <=7&nbsp;BEGIN&nbsp;&nbsp; &nbsp; SET @CurrentDate = DATEADD(DD,-(DATEPART(DD,@CurrentDate)-1),@CurrentDate)&nbsp;END&nbsp;PRINT datepart(dd,@CurrentDate)/7&nbsp; +1SET @CurrentDate = DATEADD(DAY, 7, @CurrentDate);&nbsp;END

Answer 2

请将此用于具有周开始日期和结束日期的连击结果DECLARE @StartDate&nbsp; &nbsp;datetime,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; @EndDate&nbsp; &nbsp; &nbsp;datetime,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; @CurrentDate datetime,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; @IsTrue&nbsp; &nbsp; &nbsp; BIT=0&nbsp;SET @StartDate = '2019-11-01'&nbsp;SET @EndDate = '2019-12-14'&nbsp;SET @CurrentDate=@StartDate&nbsp;WHILE ( @CurrentDate <= @EndDate )&nbsp;&nbsp; BEGIN&nbsp;&nbsp; &nbsp; &nbsp; IF( Format(@CurrentDate, 'MM') != Format(@StartDate, 'MM')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND @IsTrue = 0 )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; BEGIN&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET @CurrentDate=Dateadd(month, Datediff(month, 0, @CurrentDate), 0)&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PRINT @CurrentDate&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET @IsTrue=1&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; END&nbsp;&nbsp; &nbsp; &nbsp; SELECT CONVERT(VARCHAR(20), ( Datepart(week, @CurrentDate) -&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Datepart(week,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Dateadd(day, 1, Eomonth(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @CurrentDate, -1))) ) + 1),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;@CurrentDate,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Dateadd(day, 6, @CurrentDate)&nbsp;&nbsp; &nbsp; &nbsp; PRINT( CONVERT(VARCHAR(20), ( Datepart(week, @CurrentDate) -&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Datepart(week,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Dateadd(day, 1, Eomonth(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @CurrentDate, -1))) ) + 1) )&nbsp;&nbsp; &nbsp; &nbsp; SET @CurrentDate = Dateadd(day, 7, @CurrentDate);&nbsp;&nbsp; END&nbsp;