如何完成最高和最低时间 [英] How Do I Round Off To Highest And Lowest Time

问题描述

美好的一天

我试图将时间缩短到指定值的最高值

如果我的时间是10:05，如果我的最高分钟间隔为5它应该只有10:05但是它给了我

10:10这是假的

  DECLARE   @ Time   AS  TIME = '  10:05' 
  DECLARE   @ RoundToMin   AS  INT = 5; 
  DECLARE   @ Hours   AS   INT ; 
 
  SET   @ Hours  =（ SELECT  Datepart（hh， @ Time ））
 
  DECLARE   @ MIN   AS   INT  
  DECLARE   @ FinalTime   AS   DATETIME ; 
 
  SET   @ MIN  = Datepart（n， @ Time ）
 
  IF   CONVERT （  INT ， CONVERT （ VARCHAR （ 90 ），（（ @ MIN  /  @ RoundToMin  ）*  @ RoundToMin ）+  @ RoundToMin ））> =  60  
  BEGIN  
  SET   @ Hours  = @ Hours +  1 ; 
  SET  @ FinalTime =  CONVERT （ TIME （ 7 ）， CONVERT （ VARCHAR （ 90 ）， @ Hours ）+ ' ：' 
 +  CONVERT （ VARCHAR （ 90 ）， 00 ））; 
  END  
  ELSE  
  BEGIN  
  SET  @ FinalTime =  CONVERT （ TIME （ 7 ）， CONVERT （ VARCHAR （ 90 ）， @ Hours ）+ ' ：' 
 +  CONVERT （ VARCHAR （ 90 ），（（ @ MIN  / < span class =code-sdkkeyword> @ RoundToMin ）*  @ RoundToMin ）+  @ RoundToMin ））; 
  END  
 
  SELECT   @FinalTime  

引用：

功能将最长时间间隔向上舍入时间间隔

如果时间是10:05如果我的时间最长为5分钟，那么它应该是10:05

但是它给了我10：10：）

解决方案

参考这篇博客文章

SQL SERVER - 将时间缩短到的时间最近的分钟间隔 [ ^ ]

如果您希望结果为 10:05 然后你实际上想要被5整除的时间 - 你现在总是四舍五入到可被5整除的 next 数字。



我可能会去关于它的不同之处 - 首先检查时间是否已经是你想要的时间 - 即如果除以5就有余数。如果没有则计算需要添加多少分钟 - 再次使用除以5时的余数，并使用标准SQL函数添加这些分钟 DATEADD（）



例如：

  DECLARE   @ Time   AS  TIME = '   10:05' 
  DECLARE   @RoundToMin   AS  INT = 5 
 
  DECLARE   @MIN   AS   INT  
  DECLARE   @ FinalTime   AS   DATETIME  
 
  SET   @MIN  = Datepart（n， @ Time ）
 
  IF （ @MIN ％ @ RoundToMin ）>  0  
  SET   @ FinalTime  = DATEADD（MINUTE， @ RoundToMin   - （ @ MIN ％ @RoundToMin ）， @ Time ）
  else  
  SET   @ FinalTime  =  @ Time  
 
  SELECT   @ FinalTime  

这将返回 1月，01 1900 10:05 输入10:05和 1月，01 1900 10:10 输入10:06



然而，看看如果你输入@Time = 23:59会发生什么......你得到 1900年1月1日00：00：00 + 0000 ...这是正确的还是您希望它到达下一个日期？



如果您确实想确定已超过一天的阈值，则将 @Time 的声明更改为

 DECLARE @Time AS DATE  TIME  ='23:59'

将返回 1月， 02 1900 00：00：00 + 0000

Good Day
I am trying to round time to the highest of the value specified
If my time is 10:05 if i round of the highest minutes interval of 5 it should be 10:05 only but it is giving me
10:10 which is false

DECLARE @Time AS TIME='10:05'
DECLARE @RoundToMin AS INT=5;
DECLARE @Hours AS INT;

SET @Hours =(SELECT Datepart(hh, @Time))

DECLARE @MIN AS INT
DECLARE @FinalTime AS DATETIME;

SET @MIN = Datepart(n, @Time)

IF CONVERT(INT, CONVERT(VARCHAR(90), ( ( @MIN / @RoundToMin ) * @RoundToMin ) + @RoundToMin)) >= 60
  BEGIN
      SET @Hours =@Hours + 1;
      SET @FinalTime= CONVERT(TIME(7), CONVERT(VARCHAR(90), @Hours) + ':'
                                       + CONVERT(VARCHAR(90), 00));
  END
ELSE
  BEGIN
      SET @FinalTime= CONVERT(TIME(7), CONVERT(VARCHAR(90), @Hours) + ':'
                                       + CONVERT(VARCHAR(90), ((@MIN / @RoundToMin) * @RoundToMin) + @RoundToMin));
  END

SELECT @FinalTime

[EDIT - OP comment to non-solution]

Quote:

Function to Round Up Time to Highest Minutes Interval
if the time is 10:05 if i round of time highest of 5 minutes interval then it should be 10:05
but it giving me 10:10 :)

解决方案

refer this blog post
SQL SERVER – Function to Round Up Time to Nearest Minutes Interval[^]

If you are expecting your result to be 10:05 then you actually only want times that are divisible by 5 - you are currently always rounding up to the next number divisible by 5.

I would probably go about it differently - first check if the time is already one you want - i.e. if you divide by 5 is there a remainder. If not then calculate how many minutes need to be added - again by using the remainder when dividing by 5, and just add those minutes using a standard SQL function DATEADD()

For example:

DECLARE @Time AS TIME='10:05'
DECLARE @RoundToMin AS INT=5

DECLARE @MIN AS INT
DECLARE @FinalTime AS DATETIME

SET @MIN = Datepart(n, @Time)

IF (@MIN % @RoundToMin) > 0
  SET @FinalTime = DATEADD(MINUTE, @RoundToMin - (@MIN % @RoundToMin), @Time)
else
  SET @FinalTime = @Time

SELECT @FinalTime

This will return January, 01 1900 10:05 for your input of 10:05 and January, 01 1900 10:10 for an input of 10:06

However, watch what happens if you put in @Time = 23:59... you get January, 01 1900 00:00:00+0000 ... is that correct or do you want it to clock over to the next "day"?

If you do want to determine that you've gone over a day threshold, then change the declaration of @Time to

DECLARE @Time AS DATETIME='23:59'

which will return January, 02 1900 00:00:00+0000

这篇关于如何完成最高和最低时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！