sql 查询中的小时数舍入

Question

您在下面看到的值是用查询加载的，它们与时间戳相关。他们要求我插入的是一个四舍五入的值;上下。四舍五入必须在 15 和 30 分钟进行。

如果回合设置为 15 并且标记是在 7：59 进行的，那么如果标记在上午 8：01，则四舍五入为 8：00，我是否在此查询中实现了这一点？

查询：

select Data, string_agg(Ore, '  ') as Ore 
from ( 
select FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT') as Data,
CONCAT(DATEPART(HOUR,DataCreazione), ':', DATEPART(MINUTE, DataCreazione)) as
Ore 
from Marcatura 
where IdUtente = 2
and (Stato='Ingresso' or Stato='Uscita') 
and cast(DataCreazione as DateTime)
between cast(CONVERT(VARCHAR(10), CONVERT(date, '10-11-18', 5), 23)  as datetime)
and  cast(CONVERT(VARCHAR(10), CONVERT(date, '10-11-19', 5), 23)  as datetime)
) t 
group by Data 
order by CONVERT(datetime, Data, 105) desc

值：

05/07/2019 -- 14:45  19:27
04/07/2019 -- 11:41  11:41
07/06/2019 -- 12:39
01/06/2019 -- 8:27  8:27  8:27  8:27
18/04/2019 -- 15:41  15:41
08/04/2019 -- 11:52  11:54
01/04/2019 -- 7:25
27/03/2019 -- 21:38  21:38
23/03/2019 -- 13:32  13:32
08/03/2019 -- 21:20  21:20
04/03/2019 -- 21:48  21:48
02/03/2019 -- 8:3  8:3

如果状态等于则四舍五入到顶部，如果状态为向下舍入，例如 07：59 变为 08：00，而如果是 17：44，则变为 17：45

Answer 1

我会使用timefromparts()：

select dateadd(minute,
(case when datepart(minute, @time) not in (0, 15, 30, 45) then 15 else 0 end),
timefromparts(datepart(hour, @time),
15 * floor(datepart(minute, @time) / 15.0) % 60,
0, 0, 0
)
)

这有点复杂，因为您要四舍五入。 所以这个想法是向下舍入，然后有选择地添加 15 分钟。

Answer 2

逻辑也许比描述的要简单得多。 请尝试此方法：

编辑：将逻辑应用于您自己的查询

select Data, string_agg(Ore, '  ') as Ore 
from ( 
select FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT') as Data,
CONCAT(
(Case
when Datepart(MINUTE, DataCreazione) > 45
then Datepart(hour, Datacreazione)+1
else Datepart(hour, Datacreazione)
end)
,':'
,(case
when DATEPART(MINUTE, DataCreazione) between 0 and 15 then '15'
when DATEPART(MINUTE, DataCreazione) between 16 and 30 then '30'
when DATEPART(MINUTE, DataCreazione) between 31 and 45 then '45'
else '00' end               
)) as Ore 
from Marcatura 
where IdUtente = 2
and (Stato='Ingresso' or Stato='Uscita') 
and DataCreazione
between cast(CONVERT(VARCHAR(10), CONVERT(date, '10-11-18', 5), 23)  as datetime)
and  cast(CONVERT(VARCHAR(10), CONVERT(date, '10-11-19', 5), 23)  as datetime)
) t 
group by Data 
order by CONVERT(datetime, Data, 105) desc;

SQLFiddle here：

http://sqlfiddle.com/#!18/475202/1 http://sqlfiddle.com/#!18/23d16/13

Answer 3

这个逻辑可能会有所帮助。

declare @time time
set @time = '14:41'
select case     
when datepart(mi, @time) between 15 and 30 then dateadd(mi, -datepart(mi, @time) + 30, @time)
when datepart(mi, @time) between 16 and 45 then dateadd(mi, -datepart(mi, @time) + 45, @time)
when datepart(mi, @time) between 46 and 59 then dateadd(mi, -datepart(mi, @time) + 60, @time)
else dateadd(mi, -datepart(mi, @time), @time) end 

在查询中应用它。

select Data, string_agg(Ore, '  ') as Ore 
from ( 
select FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT') as Data,
CONVERT(VARCHAR(5), (case    
when datepart(mi, CONVERT(VARCHAR(5),DataCreazione,108)) between 15 and 30 then dateadd(mi, -datepart(mi, CONVERT(VARCHAR(5),DataCreazione,108))) + 30, CONVERT(VARCHAR(5),DataCreazione,108)))
when datepart(mi, CONVERT(VARCHAR(5),DataCreazione,108))) between 16 and 45 then dateadd(mi, -datepart(mi, CONVERT(VARCHAR(5),DataCreazione,108))) + 45, CONVERT(VARCHAR(5),DataCreazione,108)))
when datepart(mi, CONVERT(VARCHAR(5),DataCreazione,108))) between 46 and 59 then dateadd(mi, -datepart(mi, CONVERT(VARCHAR(5),DataCreazione,108))) + 60, CONVERT(VARCHAR(5),DataCreazione,108)))
else dateadd(mi, -datepart(mi, CONVERT(VARCHAR(5),DataCreazione,108))), CONVERT(VARCHAR(5),DataCreazione,108))) end), 108)  as Ore 
from Marcatura 
where IdUtente = 2
and (Stato='Ingresso' or Stato='Uscita') 
and cast(DataCreazione as DateTime)
between cast(CONVERT(VARCHAR(10), CONVERT(date, '10-11-18', 5), 23)  as datetime)
and  cast(CONVERT(VARCHAR(10), CONVERT(date, '10-11-19', 5), 23)  as datetime)
) t 
group by Data 
order by CONVERT(datetime, Data, 105) desc