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

Question

我想给出开始日期和结束日期作为输入，我需要这些日期之间的周数列表。例如：如果我给起始日期为 01/11/2019 和结束日期为 14/12/2019，我的输出将是12345123（因为前 5 个是 11 月的几周，接下来的 3 个是 12 月的几周）......让我们看另一个例子：如果我将开始日期指定为 14/11/2019 并将结束日期指定为 14/12 /2019 我的输出将是345123（因为前 3 个是 11 月的几周，接下来的 3 个是 12 月的几周） ....DECLARE @StartDate AS DATETIMEDECLARE @EndDate AS DATETIMEDECLARE @CurrentDate AS DATETIMESET @StartDate = '2019-11-01'SET @EndDate = '2019-12-14'SET @CurrentDate = @StartDateWHILE (@CurrentDate < @EndDate)BEGINPrint datepart(day, datediff(day, 0, @CurrentDate)/7 * 7)/7 + 1SET @CurrentDate = DATEADD(DAY, 7, @CurrentDate); END

Answer 1

你可以试试下面的脚本 -

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

IF datepart(DD,@CurrentDate) <=7 

BEGIN 

    SET @CurrentDate = DATEADD(DD,-(DATEPART(DD,@CurrentDate)-1),@CurrentDate) 

END 

PRINT datepart(dd,@CurrentDate)/7  +1

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

END

Answer 2

请将此用于具有周开始日期和结束日期的连击结果

DECLARE @StartDate   datetime, 

        @EndDate     datetime, 

        @CurrentDate datetime, 

        @IsTrue      BIT=0 

SET @StartDate = '2019-11-01' 

SET @EndDate = '2019-12-14' 

SET @CurrentDate=@StartDate 

WHILE ( @CurrentDate <= @EndDate ) 

  BEGIN 

      IF( Format(@CurrentDate, 'MM') != Format(@StartDate, 'MM') 

          AND @IsTrue = 0 ) 

        BEGIN 

            SET @CurrentDate=Dateadd(month, Datediff(month, 0, @CurrentDate), 0) 

            PRINT @CurrentDate 

            SET @IsTrue=1 

        END 

      SELECT CONVERT(VARCHAR(20), ( Datepart(week, @CurrentDate) - 

                                    Datepart(week, 

                                    Dateadd(day, 1, Eomonth( 

                                    @CurrentDate, -1))) ) + 1), 

             @CurrentDate, 

             Dateadd(day, 6, @CurrentDate) 

      PRINT( CONVERT(VARCHAR(20), ( Datepart(week, @CurrentDate) - 

                                    Datepart(week, 

                                    Dateadd(day, 1, Eomonth( 

                                    @CurrentDate, -1))) ) + 1) ) 

      SET @CurrentDate = Dateadd(day, 7, @CurrentDate); 

  END