sql 储存过程 更新数据，where datatime 毫秒问题

Question

需要时间作为条件，时间格式有毫秒。datagirdview 获取时间没有毫秒，只截至到秒。
ALTER PROC UPKJ90Detail @StatusID INT, @IOTime datetime, @UniqueId bigint, @DataFlag VARCHAR ( 50 ) AS UPDATE DetailRealData20220401 SET DataFlag = @DataFlag,StatusID = @StatusID WHERE UniqueId = @UniqueId  AND DATEDIFF(ss, IOTime, @IOTime) = 0

更新条件有UniqueId 和IOTime ，iotime不想通过毫秒只想通过秒来实现，怎么写呢？

查询用 DATEDIFF(ss, IOTime, @IOTime) = 0  可以查询到，更新就不行了，数据无变化

Answer 1

IOTime between CONVERT(varchar,@IOTime, 120 )+".000" and CONVERT(varchar,@IOTime, 120 )+".999"
试试上面条件或试试下面的，

ALTER PROC UPKJ90Detail 
@StatusID INT, 
@IOTime datetime, 
@UniqueId bigint, 
@DataFlag VARCHAR ( 50 ) 
AS 
UPDATE DetailRealData20220401 
       SET DataFlag = @DataFlag,
                StatusID = @StatusID
WHERE UniqueId = @UniqueId 
      AND  IOTime between @IOTime and cast(CONVERT(varchar, getdate(), 23) +" 23:59:59" as datetime)

查询语句

select * from DetailRealData20220401 where UniqueId = @UniqueId 
      AND  IOTime between @IOTime and cast(CONVERT(varchar, getdate(), 23) +" 23:59:59" as datetime)