T-SQL:四舍五入到最接近的 15 分钟间隔

2021-12-02 00:00:00 datetime sql sql-server

将 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 datetimes.

starttime and stoptime are SQL datetimes.

推荐答案

这里已回答 如何在 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)

相关文章