SQL 一种无需秒即可从 DateTime 值数据中提取的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8896663/
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
A way to extract from a DateTime value data without seconds
提问by Ghita
I have an sql DateTime(ms sql server) and want to extract the same date without the seconds:
e.g. 2011-11-22 12:14:58.000to become: 2011-11-22 12:14:00.000
我有一个 sql DateTime(ms sql server) 并且想提取没有秒的相同日期：例如2011-11-22 12:14:58.000成为：2011-11-22 12:14:00.000
How can I do this? I was thinking to use DATEADDin combination with DATEPARTbut seems very error prone (besides performance issues)
我怎样才能做到这一点？我想DATEADD结合使用DATEPART但似乎很容易出错（除了性能问题）
采纳答案by Rob
For a solution that truncates using strings try this:
对于使用字符串截断的解决方案，请尝试以下操作：
SELECT CAST(CONVERT(CHAR(16), GetDate(),20) AS datetime)
CHAR(16) works only if our variable is converted to ODBC canonicalformat, as shown above by using 20as the format specifier.
CHAR(16) 仅在我们的变量转换为ODBC 规范格式时才起作用，如上所示，20用作格式说明符。
DECLARE @date DateTime = '2011 Nov 22 12:14:55';
SELECT CONVERT(Char(16), @date ,20) AS datetime
Results:
结果：
| datetime |
|------------------|
| 2011-11-22 12:14 |
Then you simply cast back to a DateTime type to continue using the value.
然后您只需转换回 DateTime 类型以继续使用该值。
NOTE:This is only viable for data types that do not carry TimeZone info. Also type conversions to VarChar and back are usually LESSperformant than using DateTime functions that use numeric operations internally.
Consider other solutions posted if performance is a concern or if you must retain timezone information.
注意：这仅适用于不携带时区信息的数据类型。此外，与使用内部使用数字运算的 DateTime 函数相比，到 VarChar 和返回的类型转换的性能通常较低。
如果需要考虑性能或您必须保留时区信息，请考虑发布的其他解决方案。
回答by t-clausen.dk
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, yourcolumn), 0) FROM yourtable
This will be effective, if you don't want a slow conversion between datatypes.
如果您不想在数据类型之间进行缓慢的转换，这将是有效的。
回答by Tomalak
DECLARE @TheDate DATETIME
SET @TheDate = '2011-11-22 12:14:58.000'
DATEADD(mi, DATEDIFF(mi, 0, @TheDate), 0)
In queries
在查询中
/* ...all records in that minute; index-friendly expression */
WHERE TheDate BETWEEN DATEADD(mi, DATEDIFF(mi, 0, @TheDate), 0)
AND DATEADD(mi, DATEDIFF(mi, 0, @TheDate) + 1, 0)
回答by Fernando Angelieri
Date and time needs carefully and not being converted as TEXT.
日期和时间需要小心，不要被转换为文本。
My personal solution:
我个人的解决方案：
CREATE FUNCTION [dbo].[fnDateTimeTruncated]
(
@datetime DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATETIMEFROMPARTS ( year(@datetime), month(@datetime), day(@datetime), DATEPART(hh,@datetime), DATEPART(mi,@datetime), 0, 0)
END
Edited:
编辑：
Regarding http://blog.waynesheffield.com/wayne/archive/2012/03/truncate-a-date-time-to-different-part/, DateAdd has a better performance. Thanks to t-clausen.dk
关于http://blog.waynesheffield.com/wayne/archive/2012/03/truncate-a-date-time-to-different-part/， DateAdd 有更好的性能。感谢 t-clausen.dk
回答by Jason Hanwith-Horden
With a little fiddling around, this seems to work well:
稍微摆弄一下，这似乎很有效：
SELECT CAST(CONVERT(CHAR(17), bl.[time],113) AS varchar(17))
Result given: 2011-11-22 12:14
给出结果：2011-11-22 12:14
The exact way I'm using it in my query as part of the selection list:
我在查询中使用它作为选择列表的一部分的确切方式：
,CAST(CONVERT(CHAR(17), bl.[time],113) AS varchar(17))
+ ' (UTC +0)' AS [TIME]
Gives me the result: 15 Dec 2017 06:43 (UTC +0)
给我结果：2017 年 12 月 15 日 06:43（UTC +0）
回答by prakash chourasia
To Round Off it:
四舍五入：
DECLARE @TheDate DATETIME;
SET @TheDate = '2019-1-2 12:14:58.400';
SELECT CAST(@TheDate AS SMALLDATETIME);
To just Truncate:
只截断：
DECLARE @TruncTheDate DATETIME;
SET @TruncTheDate = '2019-1-2 12:14:58.400';
SELECT DATEADD(mi, DATEDIFF(mi, 0, @TruncTheDate), 0);
回答by Kosix
If there is no milliseconds, than
如果没有毫秒，那么
DECLARE @dt datetime2 = '2011-11-22 12:14:58.000';
DECLARE @goalDt datetime2 = DATEADD(second,-DATEPART(second,@dt), @dt);
To remove a milliseconds part, add
要删除毫秒部分，请添加
SET @goalDt = DATEADD(millisecond,-DATEPART(millisecond,@goalDt ), goalDt dt);
