本文介绍了T-SQL:四舍五入到最接近的 15 分钟间隔的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
将 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)
这篇关于T-SQL:四舍五入到最接近的 15 分钟间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！