SQL Server 中从日期+时间获取日期的最有效方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/133081/
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
Most efficient way in SQL Server to get date from date+time?
提问by Matt Howells
回答by Tomas
I must admit I hadn't seen the floor-float conversion shown by Matt before. I had to test this out.
我必须承认，我之前没有看到过 Matt 展示的浮点数转换。我不得不对此进行测试。
I tested a pure select (which will return Date and Time, and is not what we want), the reigning solution here (floor-float), a common 'naive' one mentioned here (stringconvert) and the one mentioned here that I was using (as I thought it was the fastest).
我测试了一个纯选择（它将返回日期和时间，这不是我们想要的），这里的统治解决方案（floor-float），这里提到的一个常见的“天真”（stringconvert）和这里提到的那个我是使用（因为我认为它是最快的）。
I tested the queries on a test-server MS SQL Server 2005 running on a Win 2003 SP2 Server with a Xeon 3GHz CPU running on max memory (32 bit, so that's about 3.5 Gb). It's night where I am so the machine is idling along at almost no load. I've got it all to myself.
我在运行在 Win 2003 SP2 服务器上的测试服务器 MS SQL Server 2005 上测试了查询，该服务器使用 Xeon 3GHz CPU，在最大内存（32 位，所以大约 3.5 Gb）上运行。这是我所在的晚上，所以机器几乎空载地空转。我自己全搞定了。
Here's the log from my test-run selecting from a large table containing timestamps varying down to the millisecond level. This particular dataset includes dates ranging over 2.5 years. The table itself has over 130 million rows, so that's why I restrict to the top million.
这是我的测试运行的日志，该日志从包含变化到毫秒级别的时间戳的大表中选择。这个特定的数据集包括超过 2.5 年的日期。该表本身有超过 1.3 亿行，所以这就是我限制为前一百万行的原因。
SELECT TOP 1000000 CRETS FROM tblMeasureLogv2
SELECT TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME) FROM tblMeasureLogv2
SELECT TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120) FROM tblMeasureLogv2
SELECT TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0) FROM tblMeasureLogv2
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 422 ms, elapsed time = 33803 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 625 ms, elapsed time = 33545 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 1953 ms, elapsed time = 33843 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 531 ms, elapsed time = 33440 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server 解析和编译时间：CPU 时间 = 0 毫秒，已用时间 = 1 毫秒。
（1000000 行受影响）表“tblMeasureLogv2”。扫描计数1，逻辑读4752，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。
SQL Server 执行时间：CPU 时间 = 422 毫秒，已用时间 = 33803 毫秒。
（1000000 行受影响）表“tblMeasureLogv2”。扫描计数1，逻辑读4752，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。
SQL Server 执行时间：CPU 时间 = 625 毫秒，已用时间 = 33545 毫秒。
（1000000 行受影响）表“tblMeasureLogv2”。扫描计数1，逻辑读4752，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。
SQL Server 执行时间：CPU 时间 = 1953 毫秒，已用时间 = 33843 毫秒。
（1000000 行受影响）表“tblMeasureLogv2”。扫描计数1，逻辑读4752，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。
SQL Server 执行时间：CPU 时间 = 531 毫秒，已用时间 = 33440 毫秒。SQL Server 解析和编译时间：CPU 时间 = 0 毫秒，已用时间 = 1 毫秒。
SQL Server 执行时间：CPU 时间 = 0 毫秒，已用时间 = 1 毫秒。
What are we seeing here?
我们在这里看到了什么？
Let's focus on the CPU time (we're looking at conversion), and we can see that we have the following numbers:
让我们关注 CPU 时间（我们正在研究转换），我们可以看到我们有以下数字：
Pure-Select: 422
Floor-cast: 625
String-conv: 1953
DateAdd: 531
From this it looks to me like the DateAdd (at least in this particular case) is slightly faster than the floor-cast method.
由此看来，DateAdd（至少在这种特殊情况下）比 floor-cast 方法稍快。
Before you go there, I ran this test several times, with the order of the queries changed, same-ish results.
在你去那里之前，我运行了几次这个测试，查询的顺序改变了，结果相同。
Is this something strange on my server, or what?
这在我的服务器上是不是很奇怪，还是什么？
回答by George Mastros
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
DateDiff(Day, 0, GetDate()) is the same as DateDiff(Day, '1900-01-01', GetDate())
DateDiff(Day, 0, GetDate()) 与 DateDiff(Day, '1900-01-01', GetDate()) 相同
Since DateDiff returns an integer, you will get the number of days that have elapsed since Jan 1, 1900. You then add that integer number of days to Jan 1, 1900. The net effect is removing the time component.
由于 DateDiff 返回一个整数，您将获得自 1900 年 1 月 1 日以来经过的天数。然后将该整数天数添加到 1900 年 1 月 1 日。最终效果是删除时间组件。
I should also mention that this method works for any date/time part (like year, quarter, month, day, hour, minute, and second).
我还应该提到这种方法适用于任何日期/时间部分（如年、季度、月、日、小时、分钟和秒）。
Select DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)
Select DateAdd(Quarter, DateDiff(Quarter, 0, GetDate()), 0)
Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
Select DateAdd(Hour, DateDiff(Hour, 0, GetDate()), 0)
Select DateAdd(Second, DateDiff(Second, '20000101', GetDate()), '20000101')
The last one, for seconds, requires special handling. If you use Jan 1, 1900 you will get an error.
最后一个，几秒钟，需要特殊处理。如果使用 1900 年 1 月 1 日，则会出现错误。
Difference of two datetime columns caused overflow at runtime.
两个日期时间列的差异导致运行时溢出。
You can circumvent this error by using a different reference date (like Jan 1, 2000).
您可以通过使用不同的参考日期（如 2000 年 1 月 1 日）来规避此错误。
回答by Matt Howells
select cast(floor(cast(@datetime as float)) as datetime)
Works because casting a datetime to float gives the number of days (including fractions of a day) since Jan 1, 1900. Flooring it removes the fractional days and leaves the number of whole days, which can then be cast back to a datetime.
之所以有效，是因为将 datetime 转换为 float 会给出自 1900 年 1 月 1 日以来的天数（包括一天的小数）。Flooring 它删除小数天数并留下整天数，然后可以将其转换回日期时间。
回答by BrianMichaels
in SQL server 2012 use
在 SQL Server 2012 中使用
select cast(getdate() as date)
回答by BrianMichaels
select cast(getdate()as varchar(11))as datetime
回答by Andrew dh
CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME)performs the best by far. you can see the proof & tests when getting the date without time in sql server
CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME)迄今为止表现最好。在 sql server 中获取没有时间的日期时，您可以看到证明和测试
回答by Erick B
To get YYYY-MM-DD, use:
要获得 YYYY-MM-DD，请使用：
select convert(varchar(10), getdate(), 120)
Edit:Oops, he wants a DateTime instead of a string. The equivalent of TRUNC() in Oracle. You can take what I posted and cast back to a DateTime:
编辑：糟糕，他想要一个 DateTime 而不是一个字符串。相当于 Oracle 中的 TRUNC()。您可以将我发布的内容转换回 DateTime：
select convert(datetime, convert(varchar(10), getdate(), 120) , 120)
回答by Ricardo C
CONVERT, FLOOR ,and DATEDIFF will perform just the same.
CONVERT、FLOOR 和 DATEDIFF 将执行相同的操作。
How to return the date part only from a SQL Server datetime datatype
回答by BlackWasp
Three methods described in the link below. I haven't performance tested them to determine which is quickest.
下面的链接中描述了三种方法。我还没有对它们进行性能测试以确定哪个最快。
回答by Turnkey
CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
