T-SQL 将日期时间修剪到最近的日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/538412/
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 to trim a datetime to the nearest date?
提问by Allain Lalonde
Duplicate ofWhat's the BEST way to remove the time portion of a datetime value (SQL Server)?
删除日期时间值 (SQL Server) 的时间部分的最佳方法是什么？
I have a column that tracks when things are created using a datetime, but I'd like to generate a report that groups them by day, so I need a way of nulling out the time component of a datetime column.
我有一列使用日期时间跟踪创建事物的时间，但我想生成一个按天分组的报告，因此我需要一种方法来消除日期时间列的时间组件。
How do I do this?
我该怎么做呢？
采纳答案by SQLMenace
One way is to change getdate()to your column name,
一种方法是更改getdate()为您的列名，
select dateadd(dd, datediff(dd, 0, getdate())+0, 0)
回答by pieman72
Why not convert straight to date:
为什么不直接转换为日期：
select convert(date, getdate())
This truncates days, not rounds. To round Days do this:
这会截断天数，而不是回合。要舍入 Days，请执行以下操作：
select convert(date, getdate() + 0.5)
回答by richardtallent
Here's another solution:
这是另一个解决方案：
SELECT CAST( FLOOR( CAST( GETDATE() AS float) ) AS smalldatetime)
回答by notnot
This is a simple way to get the date (as a string) from a datetime:
这是从日期时间获取日期（作为字符串）的简单方法：
convert(varchar, <the date field/value/etc>, 101)
But note that ordering on this field will be alphabetical rather than by date since it's now a string
但请注意，此字段的排序将按字母顺序而不是按日期排序，因为它现在是一个字符串
回答by Austin Salonen
Yes. There are many formats to choose from so I'll link it instead.
是的。有很多格式可供选择，所以我将链接它。
http://library.cirr.com/Microsoft/SQL-Server-v7/html/ca-co_1.htm
http://library.cirr.com/Microsoft/SQL-Server-v7/html/ca-co_1.htm
If you wish to zero out the time like your post implies, you can try this:
如果你想像你的帖子暗示的那样将时间归零，你可以试试这个：
select cast(convert(varchar, getdate(), 101) as datetime)
回答by Gordon Bell
declare @CurrentDate datetime
set @CurrentDate = dateadd(dd, datediff(dd, 0, getdate()), 0)
--or--
- 或者 -
select dateadd(dd, datediff(dd, 0, MyDateColumn), 0) as DateOnly
from tblX
回答by Allain Lalonde
In my searches I came across the following solution, it strips time out of UTC time only, but I found it interesting, so I thought someone else would too:
在我的搜索中，我遇到了以下解决方案，它仅从 UTC 时间中去除了时间，但我发现它很有趣，所以我认为其他人也会这样做：
FUNCTION TrimDate(@dt AS DATETIME) RETURNS DATETIME
BEGIN
RETURN CAST(CAST((@dt - 0.500000038580247) AS INT) AS DATETIME)
END
I would presume that it runs quickly since all it's doing is rounding and casting.
我认为它运行得很快，因为它所做的只是四舍五入和铸造。
回答by C_BB
I simply do cast(getdate() as date)
我只是做 cast(getdate() as date)
