我必须承认我以前没有见过 Matt 展示的浮点数转换.我不得不对此进行测试.

我测试了一个纯选择(它将返回日期和时间，这不是我们想要的)，这里的统治解决方案(floor-float)，这里提到的一个常见的天真"(stringconvert)和这里提到的那个我正在使用的(因为我认为它是最快的).

我在运行在 Win 2003 SP2 服务器上的测试服务器 MS SQL Server 2005 上测试了查询，该服务器配备了 Xeon 3GHz CPU，在最大内存(32 位，所以大约 3.5 Gb)上运行.这是我所在的晚上，所以机器几乎空载地空转.一切都是我自己的.

这是我的测试运行的日志，该日志从包含变化到毫秒级别的时间戳的大表中选择.这个特定的数据集包括超过 2.5 年的日期.该表本身有超过 1.3 亿行，因此我将其限制为前 100 万行.

SELECT TOP 1000000 CRETS FROM tblMeasureLogv2从 tblMeasureLogv2 中选择 TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME)从 tblMeasureLogv2 中选择 TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120)从 tblMeasureLogv2 中选择 TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0)

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 毫秒.

我们在这里看到了什么?

让我们关注 CPU 时间(我们正在研究转换)，我们可以看到我们有以下数字:

纯选择:422落地人数:625字符串转换:1953日期添加:531

在我看来，DateAdd(至少在这种特殊情况下)比 floor-cast 方法稍快.

在你去之前，我运行了几次这个测试，查询的顺序改变了，结果一样.

这在我的服务器上是不是很奇怪，还是什么?

In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'?

Duplicated here.

I must admit I hadn't seen the floor-float conversion shown by Matt before. I had to test this out.

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).

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.

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.

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.

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:

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.

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?