自我LV1

SQL語法如下:

只要修改一下

SET @SDate = '2021-01-04'
SET @EDate = '2021-12-31'

就可以找出該區間的週次跟週次日期區間

 

SET DATEFIRST 1
DECLARE @TEMPDate DATETIME
DECLARE @SDate DATETIME
DECLARE @EDate DATETIME

DECLARE @WKindex INT
DECLARE @TEMPtable TABLE
    (
	  YEARS INT ,
      WeekS INT ,
      FDay DATE ,
      EDay DATE
    )

SET @SDate = '2021-01-04'
SET @EDate = '2021-12-31'

SET @TEMPDate = @SDate
DECLARE @YearFistWK INT= DATEPART(dw, @TEMPDate)
SET @WKindex = ( SELECT   DATEPART(WEEK, @TEMPDate))-1


IF ( @YearFistWK = 7 )
    BEGIN 
        SET @WKindex = @WKindex + 1
    END
WHILE @TEMPDate <= @EDate
    BEGIN
        INSERT  INTO @TEMPtable
                SELECT  YEAR(@TEMPDate),
						@WKindex ,
                        a.FDay ,
                        b.EDay
                FROM    ( SELECT    1 AS ID ,
                                    DATEADD(wk, DATEDIFF(wk, 0, @TEMPDate), 0) AS FDay
                        ) a
                        LEFT JOIN ( SELECT  1 AS ID ,
                                            DATEADD(wk,
                                                    DATEDIFF(wk, 0, @TEMPDate),
                                                    6) AS EDay
                                  ) b ON a.ID = b.ID

        SET @TEMPDate = DATEADD(DAY, 7, @TEMPDate)

        SET @WKindex = @WKindex + 1

    END

SELECT  *
FROM    @TEMPtable

 

自我LV~