gpt4 book ai didi

sql-server - 舍入时间为 5 分钟最接近的 SQL Server

转载 作者:行者123 更新时间:2023-12-02 21:23:34 24 4
gpt4 key购买 nike

我不知道它对某人是否有用,但我疯狂地寻找解决方案,最终自己做了。这是一个函数(根据作为参数传递的日期),返回相同的日期和最接近的 5 倍数的近似时间。这是一个缓慢的查询,所以如果有人有更好的解决方案,欢迎。一句问候。

CREATE FUNCTION [dbo].[RoundTime] (@Time DATETIME) RETURNS DATETIME
AS
BEGIN
DECLARE @min nvarchar(50)
DECLARE @val int
DECLARE @hour int
DECLARE @temp int
DECLARE @day datetime
DECLARE @date datetime

SET @date = CONVERT(DATETIME, @Time, 120)

SET @day = (select DATEADD(dd, 0, DATEDIFF(dd, 0, @date)))
SET @hour = (select datepart(hour,@date))
SET @min = (select datepart(minute,@date))

IF LEN(@min) > 1
BEGIN
SET @val = CAST(substring(@min, 2, 1) as int)
END
else
BEGIN
SET @val = CAST(substring(@min, 1, 1) as int)
END

IF @val <= 2
BEGIN
SET @val = CAST(CAST(@min as int) - @val as int)
END
else
BEGIN
IF (@val <> 5)
BEGIN
SET @temp = 5 - CAST(@min%5 as int)
SET @val = CAST(CAST(@min as int) + @temp as int)
END

IF (@val = 60)
BEGIN
SET @val = 0
SET @hour = @hour + 1
END

IF (@hour = 24)
BEGIN
SET @day = DATEADD(day,1,@day)
SET @hour = 0
SET @min = 0
END
END

RETURN CONVERT(datetime, CAST(DATEPART(YYYY, @day) as nvarchar) + '-' + CAST(DATEPART(MM, @day) as nvarchar) + '-' +
CAST(DATEPART(dd, @day) as nvarchar) + ' ' + CAST(@hour as nvarchar) + ':' + CAST(@val as nvarchar), 120)

结束

最佳答案

您可以计算自纪元以来的分钟数:

datediff(minute,0,getdate())

除以整数 15,四舍五入为 15 分钟:

datediff(minute,0,getdate()) / 15 * 15

并将纪元添加回来:

dateadd(minute, datediff(minute,0,getdate()) / 15 * 15, 0)

例如:

select  dateadd(minute, datediff(minute,0,getdate()) / 15 * 15, 0)
-->
2013-11-12 10:45:00.000

关于sql-server - 舍入时间为 5 分钟最接近的 SQL Server,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19925476/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com