从 SQL Server 中的年份中获取所有开始日期结束日期和周数 [英] Get all start date end date and week number from the year in SQL Server
本文介绍了从 SQL Server 中的年份中获取所有开始日期结束日期和周数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想从 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 |
+---------------------+---------------------+------+
这篇关于从 SQL Server 中的年份中获取所有开始日期结束日期和周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
