在表格中插入年份、周数和两个日期之间的周开始日期，给定一周的第一天

Question

我有两个日期，分别是2008年(考虑2008-01-01(和2025年(考虑2025-12-31(，我需要能够在具有这些列的表中添加数据。

| Year | WeekNumber | WeekStartdate |

例如，假设一周的第一天是默认的/周日：

Year	WeekNumber	Week Startdate
2008年	1	208-01-06
2008	2	208-01-13
2008年	3年		2008-01年

这里有一个非常简单的基于集合的解决方案，应该可以满足您的需求。我假设您没有Numbers表，所以它通过CTE创建一个，然后只执行一些DATE函数。

DECLARE
@DateStart DATE = '2008-01-01'
,@DateEnd DATE = '2025-12-31'
,@FirstDayOfWeek TINYINT = 3
;
WITH N1 (Number) AS (SELECT 1 UNION ALL SELECT 1)
,N2 (Number) AS (SELECT N1.Number FROM N1 CROSS JOIN N1 AS N2)
,N3 (Number) AS (SELECT N2.Number FROM N2 CROSS JOIN N2 AS N3)
,N4 (Number) AS (SELECT N3.Number FROM N3 CROSS JOIN N3 AS N4)
,N5 (Number) AS (SELECT N4.Number FROM N4 CROSS JOIN N4 AS N5)
,Numbers AS (SELECT Number = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
SELECT
WYear = YEAR(DTE.WSDate)
,Wkno = ROW_NUMBER() OVER(PARTITION BY YEAR(DTE.WSDate) ORDER BY DTE.WSDate)
,DTE.WSDate
FROM Numbers AS NMS
CROSS APPLY
(
VALUES(DATEADD(day,Number-1,@dateStart))
) DTE (WSDate)
WHERE DTE.WSDate < @DateEnd
AND DATEPART(weekday,DTE.WSDate) = @FirstDayOfWeek
;

如果你真的需要能够计算周数-使用类似于ISO根据一周的不同开始日期生成周数的逻辑，那么你可以查看以下代码：

Declare @start_day int = 0
, @start_date datetime = '2000-12-25'
, @end_date datetime = '2099-12-31';
/*
@start_day = day of week to calculate
0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

*/
With t(n)
As (
Select t.n 
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, dates (inputDate)
As (
Select Top (datediff(day, @start_date, @end_date) + 1) 
dateadd(day, checksum(row_number() over(Order By @@spid) - 1), @start_date + @start_day - cast(@start_day As bit))
From t t1, t t2, t t3, t t4
)
, weekNumbers
As (
Select d.inputDate
, week_day       = datepart(weekday, d.inputDate)
, us_week        = datepart(week, d.inputDate)
, iso_week       = datepart(iso_week, d.inputDate)
, week_number    = (datepart(dayofyear, (((cast(d.inputDate As int) + 53691 - @start_day) / 7 * 7)) - 53687) + 6) / 7
, week_number_0  = (datepart(dayofyear, (((dt.intDate + iif(dt.intDate > -8, 8, 2) - @start_day) / 7 * 7)) - 4) + 6) / 7
, week_number_d0 = (datepart(dayofyear, dateadd(day, (((d2.intDate + 8 - @start_day) / 7 * 7)) - 4, cast('0001-01-01' As date))) + 6) / 7
, dt.intDate
, d2_intDate     = d2.intDate
From dates            d
Cross Apply (Values (cast(d.inputDate As int)))                                               As dt(intDate)
Cross Apply (Values (datediff(day, cast('0001-01-01' As date), cast(d.inputDate As date))))   As d2(intDate)
)
Select *
From weekNumbers;

"week_number"列的公式允许从1753-01-01到9999-12-31的日期时间。如果您使用的是日期或日期时间2数据类型，则"week_number_d0"适用于从0001-01-01到9999-12-31的所有日期。