如何获取完整年份的完整星期编号,开始日期和结束日期。
注意
周从星期日到星期六开始
在同一时间第1周(例如:01-01-2020至04-01-2020)和上周应为(例如:27-12-2020至31-12-2020)
预期结果
WeekNo WeekStartDate WeekEndDate
===================================
1 2019-01-01 2019-01-05
2 2019-01-06 2019-01-12
3 2019-01-13 2019-01-19
4 2019-01-20 2019-01-26
5 2019-01-27 2019-02-02
6 2019-02-03 2019-02-09
7 2019-02-10 2019-02-16
8 2019-02-17 2019-02-23
9 2019-02-24 2019-03-02
...
...upto end of the year
其实我想这其中也rextester
如果仅对2019年感兴趣,那么以下代码将完全生成您想要的内容:
DECLARE @weekNum INT = 1;
WITH Weeks AS (
SELECT @weekNum AS WeekNo
UNION ALL
SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= 53
)
SELECT WeekNo,
CASE
WHEN WeekStartDate < '2019-01-01' THEN CONVERT(DATE, '2019-01-01')
ELSE CONVERT(DATE, WeekStartDate)
END AS WeekStartDate,
CASE
WHEN WeekEndDate > '2019-12-31' THEN CONVERT(DATE, '2019-12-31')
ELSE CONVERT(DATE, WeekEndDate)
END AS WeekEndDate
FROM (
SELECT WeekNo,
DATEADD(WEEK, WeekNo - 1, '2018-12-30') AS WeekStartDate,
DATEADD(WEEK, WeekNo - 1, '2019-01-05') AS WeekEndDate
FROM Weeks
) a
输出:
WeekNo WeekStartDate WeekEndDate
1 2019-01-01 2019-01-05
2 2019-01-06 2019-01-12
3 2019-01-13 2019-01-19
4 2019-01-20 2019-01-26
5 2019-01-27 2019-02-02
6 2019-02-03 2019-02-09
7 2019-02-10 2019-02-16
8 2019-02-17 2019-02-23
...
51 2019-12-15 2019-12-21
52 2019-12-22 2019-12-28
53 2019-12-29 2019-12-31
编辑以下关于可变开始和结束日期的OP评论
根据OP关于更改开始日期和结束日期的评论,我重新审视了代码,使其可以在任意两个日期之间使用:
DECLARE @startDate DATE = CONVERT(DATE, '2019-01-01');
DECLARE @endDate DATE = CONVERT(DATE, '2019-12-31');
DECLARE @weekNum INT = 1;
WITH Weeks AS (
SELECT @weekNum AS WeekNo
UNION ALL
SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= DATEDIFF(WEEK, @StartDate, @EndDate) + 1
)
SELECT WeekNo,
CASE
WHEN WeekStartDate < @startDate THEN @startDate
ELSE CONVERT(DATE, WeekStartDate)
END AS WeekStartDate,
CASE
WHEN WeekEndDate > @endDate THEN @endDate
ELSE CONVERT(DATE, WeekEndDate)
END AS WeekEndDate
FROM (
SELECT WeekNo,
DATEADD(WEEK, WeekNo - 1, OffsetStartDate) AS WeekStartDate,
DATEADD(WEEK, WeekNo - 1, OffsetEndDate) AS WeekEndDate
FROM Weeks
INNER JOIN (
SELECT CASE
WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN @startDate
ELSE DATEADD(DAY, 1 - DATEPART(WEEKDAY, @startDate), @startDate)
END AS OffsetStartDate,
CASE
WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN DATEADD(DAY, 6, @startDate)
ELSE DATEADD(DAY, 7 - DATEPART(WEEKDAY, @startDate), @startDate)
END AS OffsetEndDate
) a ON 1 = 1
) a
只需修改@startDate
并@endDate
反映所需的开始和结束日期即可。字符串的格式为YYYY-MM-DD
。
这将在两个日期之间输出可变的星期数,从指定的日期开始和结束(根据需要创建部分星期)。希望根据要求。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句