使用函数将T-SQL日期时间四舍五入到最近的分钟和最近的小时

124 人关注

在SQL server 2008中，我想用2008中的现有函数将日期列四舍五入到最近的小时和最近的分钟。

对于这个列的值 2007-09-22 15:07:38.850 ，输出将看起来像。

2007-09-22 15:08 -- nearest minute
2007-09-22 15    -- nearest hour


         3
         
         个评论


           
            最接近分钟的例子不应该是15:08吗？  因为1分钟内的秒数是60...


           
            Mr Wednesday
           
           ：


           
            你在编辑他的问题时，是不是故意不纠正这个错误，以便你能发表这样的评论？


           
            @MrWednesday 你知道在编辑和该评论之间有超过10分钟的时间。我想这个想法是在之后发生的。

sql


         sql-server


         tsql


         sql-server-2008


        4
        
        个回答


          已采纳


         0
         
         人赞同


          declare @dt datetime
set @dt = '09-22-2007 15:07:38.850'
select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)
2007-09-22 15:07:00.000
2007-09-22 15:00:00.000
以上只是截断了秒和分钟，产生了问题中要求的结果。  正如@OMG Ponies所指出的，如果你想向上/向下取整，那么你可以分别增加半分钟或半小时，然后截断。
select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)
你就会得到。
2007-09-22 15:08:00.000
2007-09-22 15:00:00.000
在SQL Server 2008中添加日期数据类型之前，我会使用上述方法来截断数据时间的时间部分，只得到日期。  这个想法是要确定有关日期时间和一个固定的时间点之间的天数（0 ，隐含地将其转换为1900-01-01 00:00:00.000 ）。
declare @days int
set @days = datediff(day, 0, @dt)
然后把这个天数加到固定的时间点上，就可以得到原来的日期，并把时间设置为00:00:00.000 。
select dateadd(day, @days, 0)
或者更简洁的说。
select dateadd(day, datediff(day, 0, @dt), 0)
使用不同的日期部分（例如：hour,mi ）将相应地工作。


           
            
             
              
               
                
                 
                  
                   Eric Twilegar
                  
                  ：


           
            
             
              
               
                
                 
                  
                   我怀疑其他人会遇到这个问题，但如果你试图向上四舍五入到最接近的第二，并增加500毫秒，你会想做 datediff(second, '1/1/2000', .... vs datediff(second, 0 ....，因为你会得到一个溢出错误。从0开始的秒数太大，我猜。


           
            
             
              
               
                
                 
                  
                   Corwin Newall
                  
                  ：


           
            
             
              
               
                
                 
                  
                   '把1900年1月1日以来的小时数，加到1900年1月1日'--我在Java/SQL中遇到过这种情况，在那里看起来很丑。在我的例子中，它确实应该在Java端完成。


           
            
             
              
               
                
                 
                  
                   对于使用
                   
                    datetimeoffset
                   
                   的计算，我不得不用
                   
                    TODATETIMEOFFSET('1900-01-01 00:00:00', 0)
                   
                   代替
                   
                    0
                   
                   ，以避免将当地时区强加于结果。


           
            
             
              
               
                
                 
                  
                   '10-23-2020 14:59:59.999'给出2020-10-23 15:00:00.000而'10-23-2020 14:59:59.998'2020-10-23 14:00:00.000


           
            
             
              
               
                
                 
                  
                   @mit -
                   
                    DATETIME
                   
                   的精度 "只有 "3.33μs，所以'14:59:59.999'被存储为'15:00:00.000'，'14:59:59.998'被存储为'14:59:59.997'


          
           
            
             
              
               
                
                 
                  像你的例子中那样 "四舍五入"。这将返回一个日期的varchar值。
                 
                 DECLARE @date As DateTime2
SET @date = '2007-09-22 15:07:38.850'
SELECT CONVERT(VARCHAR(16), @date, 120) --2007-09-22 15:07
SELECT CONVERT(VARCHAR(13), @date, 120) --2007-09-22 15


           
            
             
              
               
                
                 
                  
                   
                    将varchar转换为日期时间可以做到（对于小时）。
                    
                     CONVERT(datetime, CONVERT(VARCHAR(13), @date, 120)+':00:00')


          
           
            
             
              
               
                
                 
                  
                   
                   
                    Andrew Steitz
                   
                  
                  
                   发布于
                   
                   2020-01-13


          
           
            
             
              
               
                
                 
                  
                   我知道这个问题已经过时了，而且有一个公认的和另一个的答案。  我也意识到我的答案只回答了问题的一半，但对于任何想要
                   
                    四舍五入到最接近的分钟，并且只用一个函数就能得到一个与日期时间兼容的值的
                   
                   人来说。
                  
                  CAST(YourValueHere as smalldatetime);