T-SQL 日期时间使用函数四舍五入到最接近的分钟和最接近的小时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6666866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL datetime rounded to nearest minute and nearest hours with using functions
提问by user219628
In SQL server 2008, I would like to get datetime column rounded to nearest hour and nearest minute preferably with existing functions in 2008.
在 SQL Server 2008 中，我希望最好使用 2008 年的现有函数将日期时间列四舍五入到最接近的小时和最接近的分钟。
For this column value 2007-09-22 15:07:38.850, the output will look like:
对于此列 value 2007-09-22 15:07:38.850，输出将如下所示：
2007-09-22 15:08 -- nearest minute
2007-09-22 15 -- nearest hour
回答by Jeff Ogata
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)
will return
将返回
2007-09-22 15:07:00.000
2007-09-22 15:00:00.000
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:
以上只是截断了秒和分钟，产生了问题中要求的结果。正如@OMG Ponies 指出的，如果你想向上/向下取整，那么你可以分别添加半分钟或半小时，然后截断：
select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)
and you'll get:
你会得到：
2007-09-22 15:08:00.000
2007-09-22 15:00:00.000
Before the datedata 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):
在 SQL Server 2008 中添加日期数据类型之前，我将使用上述方法从日期时间中截断时间部分以仅获取日期。这个想法是确定所讨论的日期时间和固定时间点之间的天数（0，隐式转换为1900-01-01 00:00:00.000）：
declare @days int
set @days = datediff(day, 0, @dt)
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:
然后将该天数添加到固定时间点，这将为您提供原始日期，时间设置为00:00:00.000：
select dateadd(day, @days, 0)
or more succinctly:
或更简洁地说：
select dateadd(day, datediff(day, 0, @dt), 0)
Using a different datepart (e.g. hour, mi) will work accordingly.
使用不同的日期部分（例如hour, mi）将相应地起作用。
回答by Magnus
"Rounded" down as in your example. This will return a varchar value of the date.
如您的示例所示，“四舍五入”。这将返回日期的 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
回答by Andrew Steitz
I realize this question is ancient and there is an accepted and an alternate answer. I also realize that my answer will only answer half of the question, but for anyone wanting to round to the nearest minute and still have a datetime compatible value using only a single function:
我意识到这个问题很古老，并且有一个公认的替代答案。我也意识到我的答案只能回答问题的一半，但是对于任何想要四舍五入到最近的分钟并且仍然只使用一个函数的日期时间兼容值的人：
CAST(YourValueHere as smalldatetime);
For hours or seconds, use Jeff Ogata's answer (the accepted answer) above.
对于几小时或几秒钟，请使用上面 Jeff Ogata 的答案（已接受的答案）。
回答by Jam_Jam
Select convert(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, getdate), 0), 108) as Time
Select convert(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, getdate), 0), 108) as Time
will round down seconds to 00
将秒舍入到 00
