仅 MS SQL 日期没有时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/467103/
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
MS SQL Date Only Without Time
提问by regex
Question
题
Hello All,
大家好，
I've had some confusion for quite some time with essentially flooring a DateTime SQL type using T-SQL. Essentially, I want to take a DateTime value of say 2008-12-1 14:30:12 and make it 2008-12-1 00:00:00. Alot of the queries we run for reports use a date value in the WHERE clause, but I either have a start and end date value of a day and use a BETWEEN, or I find some other method.
很长一段时间以来，我一直在使用 T-SQL 对 DateTime SQL 类型进行基本划分，这让我感到有些困惑。本质上，我想将 DateTime 值设为 2008-12-1 14:30:12 并将其设为 2008-12-1 00:00:00。我们为报告运行的许多查询在 WHERE 子句中使用日期值，但我要么有一天的开始和结束日期值并使用 BETWEEN，要么我找到了其他一些方法。
Currently I'm using the following:
WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam
目前我正在使用以下内容：
WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam
However, this seems kinda clunky. I was hoping there would be something more simple like
CAST([tstamp] AS DATE)
然而，这似乎有点笨拙。我希望会有更简单的东西
CAST([tstamp] AS DATE)
Some places online recommend using DATEPART() function, but then I end up with something like this:
网上有些地方推荐使用 DATEPART() 函数，但后来我得到了这样的结果：
WHERE DATEPART(year, [tstamp]) = DATEPART(year, @dateParam)
AND DATEPART(month, [tstamp]) = DATEPART(month, @dateParam)
AND DATEPART(day, [tstamp]) = DATEPART(day, @dateParam)
Maybe I'm being overly concerned with something small and if so please let me know. I just want to make sure the stuff I'm writing is as efficient as possible. I want to eliminate any weak links.
也许我过于关心一些小事，如果是这样，请告诉我。我只是想确保我正在写的东西尽可能高效。我想消除任何薄弱环节。
Any suggestions?
有什么建议？
Thanks,
C
谢谢，
C
Solution
解决方案
Thanks everyone for the great feedback. A lot of useful information. I'm going to change around our functions to eliminate the function on the left hand side of the operator. Although most of our date columns don't use indexes, it is probably still a better practice.
感谢大家的精彩反馈。很多有用的信息。我将围绕我们的函数进行更改，以消除运算符左侧的函数。尽管我们的大多数日期列不使用索引，但它可能仍然是一种更好的做法。
采纳答案by SQLMenace
that is very bad for performance, take a look at Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code
这对性能非常不利，看看只有在数据库中才能通过更改几行代码获得 1000% + 改进
functions on the left side of the operator are bad
运算符左侧的功能不好
here is what you need to do
这是你需要做的
declare @d datetime
select @d = '2008-12-1 14:30:12'
where tstamp >= dateadd(dd, datediff(dd, 0, @d)+0, 0)
and tstamp < dateadd(dd, datediff(dd, 0, @d)+1, 0)
Run this to see what it does
运行它看看它做了什么
select dateadd(dd, datediff(dd, 0, getdate())+1, 0)
select dateadd(dd, datediff(dd, 0, getdate())+0, 0)
回答by JoshBerke
If you're using SQL Server 2008 it has this built in now, see this in books online
如果您使用的是 SQL Server 2008，它现在已内置，请在联机书籍中查看
CAST(GETDATE() AS date)
CAST(GETDATE() AS date)
回答by Joel Coehoorn
The Date functions posted by others are the most correct way to handle this.
其他人发布的 Date 函数是处理此问题的最正确方法。
However, it's funny you mention the term "floor", because there's a little hack that will run somewhat faster:
然而，你提到“地板”这个词很有趣，因为有一个小技巧会运行得更快：
CAST(FLOOR(CAST(@dateParam AS float)) AS DateTime)
回答by AaronS
CONVERT(date, GETDATE())and CONVERT(time, GETDATE())works in SQL Server 2008. I'm uncertain about 2005.
CONVERT(date, GETDATE())并CONVERT(time, GETDATE())在 SQL Server 2008 中工作。我不确定 2005 年。
回答by Sameer
How about this?
这个怎么样？
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
回答by Sam Saffron
Careful here, if you use anything a long the lines of WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParamit will force a scan on the table and no indexes will be used for that portion.
在这里要小心，如果你使用任何很长的行，WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam它的行将强制对表进行扫描，并且该部分不会使用任何索引。
A much cleaner way of doing this is defining a calculated column
一个更简洁的方法是定义一个计算列
create table #t (
d datetime,
d2 as
cast (datepart(year,d) as varchar(4)) + '-' +
right('0' + cast (datepart(month,d) as varchar(2)),2) + '-' +
right('0' + cast (datepart(day,d) as varchar(2)),2)
)
-- notice a lot of care need to be taken to ensure the format is comparable. (zero padding)
insert #t
values (getdate())
create index idx on #t(d2)
select d2, count(d2) from #t
where d2 between '2008-01-01' and '2009-01-22'
group by d2
-- index seek is used
This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions.
通过这种方式，您可以直接检查 d2 列，并且将使用索引，并且您不必为转换而烦恼。
回答by Lusid
Yes, T-SQL can feel extremely primitive at times, and it is things like these that often times push me to doing a lot of my logic in my language of choice (such as C#).
是的，T-SQL 有时会让人感觉非常原始，正是这样的事情常常促使我用我选择的语言（例如 C#）来做很多逻辑。
However, when you absolutely need to do some of these things in SQL for performance reasons, then your best bet is to create functions to house these "algorithms."
但是，当您出于性能原因绝对需要在 SQL 中执行这些操作时，最好的办法是创建函数来容纳这些“算法”。
Take a look at this article. He offers up quite a few handy SQL functions along these lines that I think will help you.
看看这篇文章。他沿着这些方向提供了很多方便的 SQL 函数，我认为它们会对您有所帮助。
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
回答by Mark Brackett
DATEADD(d, 0, DATEDIFF(d, 0, [tstamp]))
Edit: While this will remove the time portion of your datetime, it will also make the condition non SARGable. If that's important for this query, an indexed view or a between clause is more appropriate.
编辑：虽然这将删除您的日期时间的时间部分，但它也会使条件非 SARGable。如果这对于此查询很重要，则索引视图或 between 子句更合适。
回答by Rob Boek
Here's a query that will return all results within a range of days.
这是一个查询，将返回几天范围内的所有结果。
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = DATEADD(day, -30, GETDATE())
SET @endDate = GETDATE()
SELECT *
FROM table
WHERE dateColumn >= DATEADD(day, DATEDIFF(day, 0, @startDate), 0)
AND dateColumn < DATEADD(day, 1, DATEDIFF(day, 0, @endDate))
回答by Booji Boy
FWIW, I've been doing the same thing as you for years
FWIW，多年来我一直在做和你一样的事情
CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam
Seems to me like this is one of the better ways to strip off time in terms of flexibility, speed and readabily. (sorry). Some UDF functions as suggested can be useful, but UDFs can be slow with larger result sets.
在我看来，这是在灵活性、速度和可读性方面减少时间的更好方法之一。（对不起）。建议的一些 UDF 函数可能很有用，但 UDF 可能会因较大的结果集而变慢。
