只要您使用的是SQL Server 2012或更新版本，您就可以使用TIME数据类型来获取所需内容。

DECLARE @PunchClock TABLE (PunchID INT IDENTITY, WorkStart DATETIME, PersonelNum INT)
INSERT INTO @PunchClock(WorkStart, PersonelNum) VALUES
('2022-01-01 07:35:13.000', 13021),
('2022-01-01 08:12:15.000', 17058),
('2022-01-01 07:14:59.000', 12345),
('2022-01-01 08:30:01.000', 12345)

SELECT DATEADD(SECOND,CASE WHEN CAST(WorkStart AS TIME) BETWEEN '07:15' AND '08:30' THEN DATEDIFF(SECOND,CAST(WorkStart AS TIME),'08:00') ELSE 0 END,WorkStart) AS WorkStart, PersonelNum
  FROM @PunchClock

WorkStart               PersonelNum
-----------------------------------
2022-01-01 08:00:00.000 13021
2022-01-01 08:00:00.000 17058
2022-01-01 07:14:59.000 12345
2022-01-01 08:30:01.000 12345

此case表达式将DATETIME转换为TIME，并在SECONDS中生成DATEDIFF，直到指定时间（当它落在您的范围内时）。然后，我们将差值is秒添加到表中的DATETIME。我添加了额外的几行，以显示范围外的行如何不受影响。
编辑：
如果你想变得更聪明，你也可以使用一个查找表来定义你想要的调整，如下所示：

DECLARE @PunchClock TABLE (PunchID INT IDENTITY, WorkStart DATETIME, PersonelNum INT)
INSERT INTO @PunchClock(WorkStart, PersonelNum) VALUES
('2022-01-01 07:35:13.000', 13021),
('2022-01-01 08:12:15.000', 17058),
('2022-01-01 07:14:59.000', 12345),
('2022-01-01 08:30:01.000', 12345),
('2022-01-01 23:35:00.000', 12345),
('2022-01-01 23:59:01.000', 12345)

DECLARE @AdjustmentTimes TABLE (StartTime TIME, EndTime TIME, AdjustToTime TIME, Modifier INT)
INSERT INTO @AdjustmentTimes (StartTime, EndTime, AdjustToTime, Modifier) VALUES
('07:15', '08:30',    '08:00',    0),
('23:35', '23:59:59', '23:59:59', 1)

SELECT DATEADD(SECOND,DATEDIFF(SECOND,CAST(WorkStart AS TIME),COALESCE(a.AdjustToTime,CAST(WorkStart AS TIME)))+COALESCE(a.Modifier,0),WorkStart) AS WorkStart, PersonelNum
  FROM @PunchClock p
    LEFT OUTER JOIN @AdjustmentTimes a
      ON CAST(p.WorkStart AS TIME) BETWEEN a.StartTime AND a.EndTime

要添加新的调整，您只需要调整表中的相应行。

cast(time,workstart)是不正确的语法。强制转换操作是cast(value as type)，所以它应该是cast(workstart as time)。这是您应该能够自己完成的基本操作。
另一个问题是您必须将时间添加回基准日期值。
我倾向于通过转换为从午夜开始的秒来解决这个问题，如下所示：

dateadd(second, case when datediff(second, cast(workstart as date), workstart) between 26100 and 30600 then 28800 else datediff(second, cast(workstart as date), workstart)  end, cast(cast(workstart as date) as datetime))

这是因为在time数据类型出现之前很久我就已经在使用SQL了，而且time被显式定义为一天中的时间，而不是作为时间跨度，这意味着从单独的日期和时间值组成一个日期时间结果仍然有点奇怪（你必须首先确保它们都是日期时间值）：

cast(cast(workstart as date) as datetime) + cast(case when cast(workstart as time(0)) between '07:15:00' and '08:30:00' then '08:00:00' else cast(workstart as time(0)) end as datetime) as TimeOfDayFinal

在这里看到他们都在工作：
https://dbfiddle.uk/z8EGuDkH
该链接还显示了我如何找到要在第一个示例中使用的秒值。