T-SQL：舍入到最近15分钟间隔 [英] T-SQL: Round to nearest 15 minute interval

问题描述

将HH：MM值最接近15分钟间隔的最佳方法是什么？我不跟踪秒，所以他们没有关系。

What's the best way to round an HH:MM value to the closest 15 minute interval? I don't track seconds so they don't matter.

00:08:00 becomes 00:15:00 
00:07:00 becomes 00:00:00 
01:59:00 becomes 02:00:00 

等等。有没有一个优雅的非UDF或Case语句方法来执行此操作？

and so on. Is there an elegant, non UDF or Case statement method for doing this?

编辑：这是我用来获取上述的SQL我想要的值：

Here's the SQL I'm using to get the above values that I'd like to round:

CONVERT(CHAR(8), DATEADD(n, SUM(DATEDIFF(n, starttime, stoptime)), 0), 108)

starttime 和 stoptime 是SQL datetime s。

推荐答案

这里已回答如何在T-SQL中舍入一次，我认为它应该适合你。

This was answered here How to Round a Time in T-SQL and i think it should work for you to.

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) RETURNS datetime
AS
BEGIN
    DECLARE @RoundedTime smalldatetime, @Multiplier float

    SET @Multiplier = 24.0 / @RoundTo

    SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar, @Time, 121) AS datetime) AS float) * @Multiplier, 0) / @Multiplier

    RETURN @RoundedTime
END

-- Usage    
SELECT dbo.RoundTime('13:15', 0.5)

这篇关于T-SQL：舍入到最近15分钟间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！