sql server中时间归不到这个时间段里，求解决

img

CASE
WHEN
CONVERT(varchar(20),checkTime,120) >=
convert(varchar(20), DateAdd(hh, 8 , dateadd(week,datediff(week,0,checkTime),2) ) ,120)
AND CONVERT(varchar(20),checkTime,120)<
convert(varchar(20), DateAdd(hh, 8 , dateadd(week,datediff(week,0,checkTime),2) )+7 ,120)

THEN

convert(varchar(20), DateAdd(hh, 8 , dateadd(week,datediff(week,0,checkTime),2) ) ,120)
+'~'+
convert(varchar(20), DateAdd(hh, 8 , dateadd(week,datediff(week,0,checkTime),2) )+7 ,120)
ELSE ''
END chetime

checkTime为数据库日期字段

不晓得有些日期为神魔归不到时间段里，求解决

你week和hh的值是多少
你这里只做了一个case when then的判断，else直接赋0了，也没有多个判断，也没有循环，那可不只能匹配一小段时间吗，只要时间落到这个时间外面就会输出空字符串

你这段代码写错了，dateadd函数输出的是日期，你在后面加个整数7是什么意思？

只要计算好偏差量，这玩意不是想怎么划区间都可以么？


with t as (
select '2022-01-11 23:39:43' checkTime union all 
select '2022-01-11 23:41:37' union all 
select '2022-01-11 23:50:23' union all 
select '2022-01-11 23:52:26' union all 
select '2022-01-05 08:04:00' union all 
select '2022-01-05 08:16:54' union all 
select '2022-01-05 08:17:11' union all 
select '2022-01-12 08:17:11' union all 
select '2022-01-13 08:17:11' 
)
 
select checkTime,
DATEADD(HOUR,-64, DATEADD(week,datepart(week,DATEADD(HOUR,88,checkTime))-2,dateadd(year, datediff(year, 0, checkTime), 0)) ) 开始,
DATEADD(HOUR,104, DATEADD(week,datepart(week,DATEADD(HOUR,88,checkTime))-2,dateadd(year, datediff(year, 0, checkTime), 0)) ) 结束,
datepart(week,DATEADD(HOUR,88,checkTime)) 分组 from t;

img