如果与最近的小时相距 1 分钟，则将 SQL 时间舍入到最接近的小时

Question

如果时间值与最近的小时相距 1 分钟，我想将 SQL 时间四舍五入到最接近的小时。（时间采用 24 小时制）

例如

23:59 - > 00:00
11:30 - > 11:30
03:59 - > 04:00
01:15 - > 01:15
12:59 - > 13:00

这是我到目前为止能够做到的，但它只是四舍五入到最接近的 1 分钟。

declare @t time 
declare @t2 time
set @t = '23:59:00.0000000';
set @t2 = '23:30:00.0000000';
select cast(dateadd(millisecond, 29999, @t) as smalldatetime) as T1, cast(dateadd(millisecond, 29999, @t2) as smalldatetime) as T2

Answer 1

如果您尝试添加一分钟，然后测试结果，则可以做出决定：

SELECT 
    cast(dateadd(millisecond, 29999, @t) as smalldatetime) as T1, 
    cast(dateadd(millisecond, 29999, @t2) as smalldatetime) as T2
    ,CASE WHEN SUBSTRING(CAST(DATEADD(MINUTE, 1, @t) as nvarchar(15)),4,2) = '00' THEN DATEADD(MINUTE, 1, @t) ELSE @t END
    ,CASE WHEN SUBSTRING(CAST(DATEADD(MINUTE, 1, @t2) as nvarchar(15)),4,2) = '00' THEN DATEADD(MINUTE, 1, @t2) ELSE @t2 END

Answer 2

这似乎是一个奇怪的要求——只是去掉 60 分钟中的一个特定分钟。 但是case语句应该做你想做的事：

select (case when datepart(hour, time) = 59
             then dateadd(hour, datediff(hour, 0, @t) + 1, 0)
             else @t
        end)

Answer 3

使用这个想法：

select case when datepart(hh,dateadd(minute,1,convert(time,'10:59'))) <> datepart(hh,convert(time,'10:59')) then dateadd(minute,1,convert(time,'10:59'))
else convert(time,'10:59') end

Answer 4

这使用 case 语句来检查分钟（而不是小时）是否@GordonLinoff :)为 59 或 0，如果是，则返回四舍五入到最接近小时的时间。否则，它只会返回时间。下面是一些示例代码

declare @t table (t time);
insert @t (t) values 
('22:58:00'),
('22:58:30'),
('22:59:00'),
('22:59:30'),
('23:00:00'),
('23:00:30'),
('23:01:00'),
('23:01:30')
select 
  t, 
  -- just return the time rounded to the nearest hour
  cast (dateadd(hour, datediff(hour, 0, dateadd(mi, 30, t)), 0) as time) as NearestHour,
  -- return the time rounded to the nearest hour ONLY IF IT"S WITHIN ONE MINUTE OF THE HOUR
  case when datepart (minute, t) in (0, 59)
        then cast (dateadd(hour, datediff(hour, 0, dateadd(mi, 30, t)), 0) as time)
        else t 
  end as Rounded
from @t

结果如下：

t                NearestHour      Rounded
---------------- ---------------- ----------------
22:58:00.0000000 23:00:00.0000000 22:58:00.0000000
22:58:30.0000000 23:00:00.0000000 22:58:30.0000000
22:59:00.0000000 23:00:00.0000000 23:00:00.0000000
22:59:30.0000000 23:00:00.0000000 23:00:00.0000000
23:00:00.0000000 23:00:00.0000000 23:00:00.0000000
23:00:30.0000000 23:00:00.0000000 23:00:00.0000000
23:01:00.0000000 23:00:00.0000000 23:01:00.0000000
23:01:30.0000000 23:00:00.0000000 23:01:30.0000000