SQL 将日期截断为仅小时/分钟
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9783745/
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
Truncate date to only hour / minute
提问by pufos
How do i trunc the date in sql server 2008 like this :
我如何像这样截断 sql server 2008 中的日期：
I have 2012-01-02 12:04:11.443and I want only to select 2012-01-02 12:00:00.000and 2012-01-02 12:04:00.000(hour and minute level)
我有2012-01-02 12:04:11.443，我只想选择2012-01-02 12:00:00.000和2012-01-02 12:04:00.000（小时和分钟级别）
回答by Mikael Eriksson
declare @D as datetime = '2012-01-02T12:04:11.443'
select dateadd(hour, datediff(hour, 0, @D), 0)
select dateadd(minute, datediff(minute, 0, @D), 0)
回答by SmartestVEGA
This seems to work:
这似乎有效：
select [Rounded Time] =
dateadd(mi,datediff(mi,0,dateadd(ss,30,a.DT)),0)
from
(
select dt = convert(datetime,'8:24:29.997')
union all
select dt = convert(datetime,'8:24:30.000')
) a
Results:
结果：
Rounded Time
------------------------------------------------------
1900-01-01 08:24:00.000
1900-01-01 08:25:00.000
(2 row(s) affected)
回答by maniek765
I'm using something like this:
我正在使用这样的东西：
DATEADD(hour,DATEDIFF(hour,'2000-01-01 00:00',[timestamp]),'2000-01-01 00:00')
You can use this formula to truncate to other levels, ie minutes:
您可以使用此公式截断到其他级别，即分钟：
DATEADD(minutes,DATEDIFF(minutes,'2000-01-01 00:00',[timestamp]),'2000-01-01 00:00')
You must remember that if you are truncating to seconds level, the maximum difference between the base date and [timestamp] is 68 years.
您必须记住，如果您要截断到秒级别，则基准日期和 [时间戳] 之间的最大差异是 68 年。
