您的位置: 首页  >  技术问答  >  从 SQL Server 中的年份中获取所有开始日期结束日期和周数

从 SQL Server 中的年份中获取所有开始日期结束日期和周数

分类: 技术问答 • 2022-04-19 19:00:42
问题描述：

我想从 2016 年 1 月的第一天开始创建开始日期、结束日期和周数.一周的第一天是星期五.下面是我想要的示例.

I want to create startdate, enddate and week number from the first January of 2016. First day of week is Friday. Here is the example below what I want.

设置一周的第一天是星期五

Set first day of week is Friday 

Start Date  EndDate WeekNumber
1/1/2016    7/1/2016    1
8/1/2016    14/1/2016   2
15/1/2016   21/1/2016   3
…..     
……      
…..     
30/12/2016  5/1/2017    53
6/1/2017    12/1/2017   1
13/1/2017   19/1/2017   2
20/1/2017   26/1/2017   3
…       
….      
…

请给我任何想法.

我可以在周五的第一天就这样

I can first day of week Friday is by this way

SET DATEFIRST 5
SELECT  DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [WeekStart],
        DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [WeekEnd]

并通过这种方式获取周数

and get week number by this way 

SELECT { fn WEEK( '2016-01-01') }

答

它生成一系列@StartDate 和@StartDate + @NumYears 之间的所有周:

It generates a serie of all weeks between @StartDate and @StartDate + @NumYears:

您可以更改开始和结束日期，但请注意 MAXRECURSION.

You can change start and end date, but take care of MAXRECURSION.

CREATE FUNCTION fnWeek(@StartDate DATETIME)
RETURNS @Weeks TABLE (StartWeekDate DATETIME, EndWeekDate DATETIME, WeekOfYear int)
AS
BEGIN
    DECLARE @NumYears int = 2;

    ;WITH genDates  
    AS (
        SELECT @StartDate AS mdate
        UNION ALL
        SELECT DATEADD(week, 1, mdate) FROM genDates WHERE DATEADD(week, 1, mdate) < DATEADD(year, @NumYears, @StartDate)
    )
    INSERT INTO @Weeks
    SELECT mdate AS StartDate, DATEADD(day,6,mdate) AS EndDate, DATEPART(week, mdate) AS WeekOfYear
    FROM genDates OPTION (MAXRECURSION 0);

    RETURN;
END
GO 

select * from fnWeek('2016-01-01');

或者@NumYears可以作为参数添加:

Or @NumYears can be added as a parameter:

CREATE FUNCTION fnWeek(@StartDate DATETIME, @NumYears int)
RETURNS @Weeks TABLE (StartWeekDate DATETIME, EndWeekDate DATETIME, WeekOfYear int)
AS
BEGIN

    ;WITH genDates  
    AS (
        SELECT @StartDate AS mdate
        UNION ALL
        SELECT DATEADD(week, 1, mdate) FROM genDates WHERE DATEADD(week, 1, mdate) < DATEADD(year, @NumYears, @StartDate)
    )
    INSERT INTO @Weeks
    SELECT mdate AS StartDate, DATEADD(day,6,mdate) AS EndDate, DATEPART(week, mdate) AS WeekOfYear
    FROM genDates 
    OPTION (MAXRECURSION 0);

    RETURN;
END
GO 

select * from fnWeek('2016-01-01', 2);


+---------------------+---------------------+------+
|    StarWeektDate    |     EndWeekDate     | Week |
+---------------------+---------------------+------+
| 01.01.2016 00:00:00 | 07.01.2016 00:00:00 |   1  |
+---------------------+---------------------+------+
| 08.01.2016 00:00:00 | 14.01.2016 00:00:00 |   2  |
+---------------------+---------------------+------+
| 15.01.2016 00:00:00 | 21.01.2016 00:00:00 |   3  |
+---------------------+---------------------+------+
| 22.01.2016 00:00:00 | 28.01.2016 00:00:00 |   4  |
+---------------------+---------------------+------+
| 29.01.2016 00:00:00 | 04.02.2016 00:00:00 |   5  |
+---------------------+---------------------+------+
| 05.02.2016 00:00:00 | 11.02.2016 00:00:00 |   6  |
+---------------------+---------------------+------+
| 12.02.2016 00:00:00 | 18.02.2016 00:00:00 |   7  |
+---------------------+---------------------+------+
| 19.02.2016 00:00:00 | 25.02.2016 00:00:00 |   8  |
+---------------------+---------------------+------+
| 26.02.2016 00:00:00 | 03.03.2016 00:00:00 |   9  |
+---------------------+---------------------+------+
| 04.03.2016 00:00:00 | 10.03.2016 00:00:00 |  10  |
+---------------------+---------------------+------+
| 11.03.2016 00:00:00 | 17.03.2016 00:00:00 |  11  |
+---------------------+---------------------+------+
| 18.03.2016 00:00:00 | 24.03.2016 00:00:00 |  12  |
+---------------------+---------------------+------+
| 25.03.2016 00:00:00 | 31.03.2016 00:00:00 |  13  |
+---------------------+---------------------+------+
| 01.04.2016 00:00:00 | 07.04.2016 00:00:00 |  14  |
+---------------------+---------------------+------+
| 08.04.2016 00:00:00 | 14.04.2016 00:00:00 |  15  |
+---------------------+---------------------+------+
| 15.04.2016 00:00:00 | 21.04.2016 00:00:00 |  16  |
+---------------------+---------------------+------+
| 22.04.2016 00:00:00 | 28.04.2016 00:00:00 |  17  |
+---------------------+---------------------+------+
| 29.04.2016 00:00:00 | 05.05.2016 00:00:00 |  18  |
+---------------------+---------------------+------+
| 06.05.2016 00:00:00 | 12.05.2016 00:00:00 |  19  |
+---------------------+---------------------+------+
| 13.05.2016 00:00:00 | 19.05.2016 00:00:00 |  20  |
+---------------------+---------------------+------+
| 20.05.2016 00:00:00 | 26.05.2016 00:00:00 |  21  |
+---------------------+---------------------+------+
| 27.05.2016 00:00:00 | 02.06.2016 00:00:00 |  22  |
+---------------------+---------------------+------+
| 03.06.2016 00:00:00 | 09.06.2016 00:00:00 |  23  |
+---------------------+---------------------+------+
| 10.06.2016 00:00:00 | 16.06.2016 00:00:00 |  24  |
+---------------------+---------------------+------+
| 17.06.2016 00:00:00 | 23.06.2016 00:00:00 |  25  |
+---------------------+---------------------+------+
| 24.06.2016 00:00:00 | 30.06.2016 00:00:00 |  26  |
+---------------------+---------------------+------+
| 01.07.2016 00:00:00 | 07.07.2016 00:00:00 |  27  |
+---------------------+---------------------+------+
| 08.07.2016 00:00:00 | 14.07.2016 00:00:00 |  28  |
+---------------------+---------------------+------+
| 15.07.2016 00:00:00 | 21.07.2016 00:00:00 |  29  |
+---------------------+---------------------+------+
| 22.07.2016 00:00:00 | 28.07.2016 00:00:00 |  30  |
+---------------------+---------------------+------+
| 29.07.2016 00:00:00 | 04.08.2016 00:00:00 |  31  |
+---------------------+---------------------+------+
| 05.08.2016 00:00:00 | 11.08.2016 00:00:00 |  32  |
+---------------------+---------------------+------+
| 12.08.2016 00:00:00 | 18.08.2016 00:00:00 |  33  |
+---------------------+---------------------+------+
| 19.08.2016 00:00:00 | 25.08.2016 00:00:00 |  34  |
+---------------------+---------------------+------+
| 26.08.2016 00:00:00 | 01.09.2016 00:00:00 |  35  |
+---------------------+---------------------+------+
| 02.09.2016 00:00:00 | 08.09.2016 00:00:00 |  36  |
+---------------------+---------------------+------+
| 09.09.2016 00:00:00 | 15.09.2016 00:00:00 |  37  |
+---------------------+---------------------+------+
| 16.09.2016 00:00:00 | 22.09.2016 00:00:00 |  38  |
+---------------------+---------------------+------+
| 23.09.2016 00:00:00 | 29.09.2016 00:00:00 |  39  |
+---------------------+---------------------+------+
| 30.09.2016 00:00:00 | 06.10.2016 00:00:00 |  40  |
+---------------------+---------------------+------+
| 07.10.2016 00:00:00 | 13.10.2016 00:00:00 |  41  |
+---------------------+---------------------+------+
| 14.10.2016 00:00:00 | 20.10.2016 00:00:00 |  42  |
+---------------------+---------------------+------+
| 21.10.2016 00:00:00 | 27.10.2016 00:00:00 |  43  |
+---------------------+---------------------+------+
| 28.10.2016 00:00:00 | 03.11.2016 00:00:00 |  44  |
+---------------------+---------------------+------+
| 04.11.2016 00:00:00 | 10.11.2016 00:00:00 |  45  |
+---------------------+---------------------+------+
| 11.11.2016 00:00:00 | 17.11.2016 00:00:00 |  46  |
+---------------------+---------------------+------+
| 18.11.2016 00:00:00 | 24.11.2016 00:00:00 |  47  |
+---------------------+---------------------+------+
| 25.11.2016 00:00:00 | 01.12.2016 00:00:00 |  48  |
+---------------------+---------------------+------+
| 02.12.2016 00:00:00 | 08.12.2016 00:00:00 |  49  |
+---------------------+---------------------+------+
| 09.12.2016 00:00:00 | 15.12.2016 00:00:00 |  50  |
+---------------------+---------------------+------+
| 16.12.2016 00:00:00 | 22.12.2016 00:00:00 |  51  |
+---------------------+---------------------+------+
| 23.12.2016 00:00:00 | 29.12.2016 00:00:00 |  52  |
+---------------------+---------------------+------+
| 30.12.2016 00:00:00 | 05.01.2017 00:00:00 |  53  |
+---------------------+---------------------+------+
| 06.01.2017 00:00:00 | 12.01.2017 00:00:00 |   1  |
+---------------------+---------------------+------+
| 13.01.2017 00:00:00 | 19.01.2017 00:00:00 |   2  |
+---------------------+---------------------+------+
| 20.01.2017 00:00:00 | 26.01.2017 00:00:00 |   3  |
+---------------------+---------------------+------+
| 27.01.2017 00:00:00 | 02.02.2017 00:00:00 |   4  |
+---------------------+---------------------+------+
| 03.02.2017 00:00:00 | 09.02.2017 00:00:00 |   5  |
+---------------------+---------------------+------+
| 10.02.2017 00:00:00 | 16.02.2017 00:00:00 |   6  |
+---------------------+---------------------+------+
| 17.02.2017 00:00:00 | 23.02.2017 00:00:00 |   7  |
+---------------------+---------------------+------+
| 24.02.2017 00:00:00 | 02.03.2017 00:00:00 |   8  |
+---------------------+---------------------+------+
| 03.03.2017 00:00:00 | 09.03.2017 00:00:00 |   9  |
+---------------------+---------------------+------+
| 10.03.2017 00:00:00 | 16.03.2017 00:00:00 |  10  |
+---------------------+---------------------+------+
| 17.03.2017 00:00:00 | 23.03.2017 00:00:00 |  11  |
+---------------------+---------------------+------+
| 24.03.2017 00:00:00 | 30.03.2017 00:00:00 |  12  |
+---------------------+---------------------+------+
| 31.03.2017 00:00:00 | 06.04.2017 00:00:00 |  13  |
+---------------------+---------------------+------+
| 07.04.2017 00:00:00 | 13.04.2017 00:00:00 |  14  |
+---------------------+---------------------+------+
| 14.04.2017 00:00:00 | 20.04.2017 00:00:00 |  15  |
+---------------------+---------------------+------+
| 21.04.2017 00:00:00 | 27.04.2017 00:00:00 |  16  |
+---------------------+---------------------+------+
| 28.04.2017 00:00:00 | 04.05.2017 00:00:00 |  17  |
+---------------------+---------------------+------+
| 05.05.2017 00:00:00 | 11.05.2017 00:00:00 |  18  |
+---------------------+---------------------+------+
| 12.05.2017 00:00:00 | 18.05.2017 00:00:00 |  19  |
+---------------------+---------------------+------+
| 19.05.2017 00:00:00 | 25.05.2017 00:00:00 |  20  |
+---------------------+---------------------+------+
| 26.05.2017 00:00:00 | 01.06.2017 00:00:00 |  21  |
+---------------------+---------------------+------+
| 02.06.2017 00:00:00 | 08.06.2017 00:00:00 |  22  |
+---------------------+---------------------+------+
| 09.06.2017 00:00:00 | 15.06.2017 00:00:00 |  23  |
+---------------------+---------------------+------+
| 16.06.2017 00:00:00 | 22.06.2017 00:00:00 |  24  |
+---------------------+---------------------+------+
| 23.06.2017 00:00:00 | 29.06.2017 00:00:00 |  25  |
+---------------------+---------------------+------+
| 30.06.2017 00:00:00 | 06.07.2017 00:00:00 |  26  |
+---------------------+---------------------+------+
| 07.07.2017 00:00:00 | 13.07.2017 00:00:00 |  27  |
+---------------------+---------------------+------+
| 14.07.2017 00:00:00 | 20.07.2017 00:00:00 |  28  |
+---------------------+---------------------+------+
| 21.07.2017 00:00:00 | 27.07.2017 00:00:00 |  29  |
+---------------------+---------------------+------+
| 28.07.2017 00:00:00 | 03.08.2017 00:00:00 |  30  |
+---------------------+---------------------+------+
| 04.08.2017 00:00:00 | 10.08.2017 00:00:00 |  31  |
+---------------------+---------------------+------+
| 11.08.2017 00:00:00 | 17.08.2017 00:00:00 |  32  |
+---------------------+---------------------+------+
| 18.08.2017 00:00:00 | 24.08.2017 00:00:00 |  33  |
+---------------------+---------------------+------+
| 25.08.2017 00:00:00 | 31.08.2017 00:00:00 |  34  |
+---------------------+---------------------+------+
| 01.09.2017 00:00:00 | 07.09.2017 00:00:00 |  35  |
+---------------------+---------------------+------+
| 08.09.2017 00:00:00 | 14.09.2017 00:00:00 |  36  |
+---------------------+---------------------+------+
| 15.09.2017 00:00:00 | 21.09.2017 00:00:00 |  37  |
+---------------------+---------------------+------+
| 22.09.2017 00:00:00 | 28.09.2017 00:00:00 |  38  |
+---------------------+---------------------+------+
| 29.09.2017 00:00:00 | 05.10.2017 00:00:00 |  39  |
+---------------------+---------------------+------+
| 06.10.2017 00:00:00 | 12.10.2017 00:00:00 |  40  |
+---------------------+---------------------+------+
| 13.10.2017 00:00:00 | 19.10.2017 00:00:00 |  41  |
+---------------------+---------------------+------+
| 20.10.2017 00:00:00 | 26.10.2017 00:00:00 |  42  |
+---------------------+---------------------+------+
| 27.10.2017 00:00:00 | 02.11.2017 00:00:00 |  43  |
+---------------------+---------------------+------+
| 03.11.2017 00:00:00 | 09.11.2017 00:00:00 |  44  |
+---------------------+---------------------+------+
| 10.11.2017 00:00:00 | 16.11.2017 00:00:00 |  45  |
+---------------------+---------------------+------+
| 17.11.2017 00:00:00 | 23.11.2017 00:00:00 |  46  |
+---------------------+---------------------+------+
| 24.11.2017 00:00:00 | 30.11.2017 00:00:00 |  47  |
+---------------------+---------------------+------+
| 01.12.2017 00:00:00 | 07.12.2017 00:00:00 |  48  |
+---------------------+---------------------+------+
| 08.12.2017 00:00:00 | 14.12.2017 00:00:00 |  49  |
+---------------------+---------------------+------+
| 15.12.2017 00:00:00 | 21.12.2017 00:00:00 |  50  |
+---------------------+---------------------+------+
| 22.12.2017 00:00:00 | 28.12.2017 00:00:00 |  51  |
+---------------------+---------------------+------+
| 29.12.2017 00:00:00 | 04.01.2018 00:00:00 |  52  |
+---------------------+---------------------+------+

相关推荐