sql - 获取每周的开始日期和结束日期
问题描述
这是我的代码
declare @startdate date='2021-01-01'
declare @enddate date='2021-12-31'
declare @week_end_date date
while (@startdate <= @enddate)
begin
if (datepart(week, @startdate) = 1)
select '01-01-2021'
else
set @startdate = DATEADD(day,1-datepart(dw,@startdate),@startdate)
select @startdate week_start_date
if (datepart(week, @startdate) = 53)
select '2021-12-31'
else
set @week_end_date = dateadd(day, 7 - datepart(dw, @startdate), @startdate)
select @week_end_date week_end_date
set @startdate = dateadd(week, 1, @startdate)
end
我想达到以下结果
week_start date week_end_date
-----------------------------------------
2021-01-01 2021-01-02
2021-01-03 2021-01-09
2021-01-10 2021-01-16
.
.
.
.
.
.
2021-12-26 2021-12-31
.
.
我正在尝试获取start_date
每周end_date
的一周,但我希望第一周的开始日期和上周的2020-01-01
结束日期为 2020-12-31
在这两种情况下else
都在运行。
解决方案
我猜你真的只需要涵盖一个完整的日历年。前两个 CTE 只是创建一个“数字表”。如有必要,您可以插入替换件。我也假设datefirst
设置为 7。
with d as (
select n from (values (0), (1), (2), (3), (4), (5), (6), (7)) t(n)
), w as (
select d0.n + d1.n * 8 as offset from d as d0 cross join d as d1
), weeks as (
select
offset + 1 as weeknum,
dateadd(week, offset, basedate) as weekstart,
dateadd(day, 6, dateadd(week, offset, basedate)) as weekend
from w cross apply (
select dateadd(day, 1 - datepart(weekday, @startdate), @startdate)
) as v(basedate)
)
select
weeknum,
case when weekstart < @startdate then @startdate else weekstart end,
case when weekend > @enddate then @enddate else weekend end
from weeks
where weekstart <= @enddate;
https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=5f900ecd26b92e4a112f191730958cae
推荐阅读
- flask - 使用 sqlalchemy、棉花糖和烧瓶更新行?
- javascript - 响应体的 concat 缓冲区未写入文件,甚至显示在 console.log 上
- arrays - 反应原生:如何附加两个对象数组
- c# - C#中的通用对象
- python-3.x - Dynamodb 在数组的映射中搜索键的值
- c - 尽管遵循以下格式规范,位图写入程序不会产生可读的图像
- jquery - jQuery toggle() ,我想单击 1 个按钮并隐藏所有其他 div 的按钮
- javascript - 使用 Flask 和 Python - 不能让消息出现和消失
- reactjs - ClassName 没有被传递给 Objects
- java - 当用户回来时如何保存视图的状态并恢复它?