T-SQL 日期时间使用函数四舍五入到最接近的分钟和最接近的小时 [英] T-SQL datetime rounded to nearest minute and nearest hours with using functions

问题描述

在 SQL Server 2008 中，我希望最好使用 2008 年的现有函数将日期时间列四舍五入到最接近的小时和最接近的分钟.

In SQL server 2008, I would like to get datetime column rounded to nearest hour and nearest minute preferably with existing functions in 2008.

对于此列值 2007-09-22 15:07:38.850，输出将如下所示:

For this column value 2007-09-22 15:07:38.850, the output will look like:

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

推荐答案

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 指出的，如果你想向上/向下舍入，那么你可以分别添加半分钟或半小时，然后截断:

The above just truncates the seconds and minutes, producing the results asked for in the question. As @OMG Ponies pointed out, if you want to round up/down, then you can add half a minute or half an hour respectively, then truncate:

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 中添加 date 数据类型之前，我会使用上面的从日期时间截断时间部分以仅获取日期的方法.这个想法是确定有问题的日期时间和固定时间点之间的天数(0，它隐式转换为 1900-01-01 00:00:00.000>):

---

Before the date data type was added in SQL Server 2008, I would use the above method to truncate the time portion from a datetime to get only the date. The idea is to determine the number of days between the datetime in question and a fixed point in time (0, which implicitly casts to 1900-01-01 00:00:00.000):

declare @days int
set @days = datediff(day, 0, @dt)

然后将该天数添加到固定时间点，这将为您提供原始日期，时间设置为 00:00:00.000:

and then add that number of days to the fixed point in time, which gives you the original date with the time set to 00:00:00.000:

select dateadd(day, @days, 0)

或更简洁:

select dateadd(day, datediff(day, 0, @dt), 0)

使用不同的日期部分(例如 hour、mi)会相应地起作用.

Using a different datepart (e.g. hour, mi) will work accordingly.

这篇关于T-SQL 日期时间使用函数四舍五入到最接近的分钟和最接近的小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！