logo标签列表

SQL Server中从日期+时间获取日期的最有效方式是什么？

sqlsql-servert-sql
80

在 MS SQL 2000 和 2005 中，如果给出一个日期时间值如 '2008-09-25 12:34:56'，最有效的方法是什么以获取只包含 '2008-09-25' 的日期时间值？

此问题已被复制到这里。

4
我非常高兴前三个答案都不同，这意味着这不是一个愚蠢的问题！ :) - Matt Howells
2
可能是重复的问题：什么是最好的方法来删除日期时间值的时间部分（SQL Server）？ - Rowland Shaw
11个回答
116

我必须承认，之前我从未见过Matt展示的floor-float转换方法。我不得不测试一下。

我测试了一个纯选择查询（会返回日期和时间，而这不是我们想要的），这里的主流解决方案（floor-float），一个常见的“幼稚”的方案（stringconvert）以及我曾经使用过的这个方案（因为我认为它是最快的）。

我在一个测试服务器上进行了查询，该服务器是运行在Windows Server 2003 SP2上的MS SQL Server 2005，配备一颗3GHz的Xeon CPU，并且内存达到最大值（32位系统，约为3.5 Gb）。现在是晚上，在我的机器上几乎没有负载。我独占了整个服务器。

以下是我的测试日志，选择一个包含时间戳的大表格，时间戳精确到毫秒级别。这个数据集包括超过2.5年的日期范围。这个表格本身有超过1.3亿行数据，所以我只对前100万行进行限制。

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解析和编译时间：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时间（我们正在查看转换），并且我们可以看到以下数字：
Pure-Select:  422
Floor-cast:   625
String-conv: 1953
DateAdd:      531

从这个结果来看，至少在这种情况下，DateAdd 的速度似乎比 floor-cast 方法稍快。

在你继续之前，我已经多次运行了此测试，并改变了查询的顺序，得到了类似的结果。

这是我的服务器出了什么问题吗？还是其他原因？

5
这一点并不奇怪。首先，在我看来，将其转换为浮点数是一种不好的做法，因为往返于日期时间类型的转换并不可靠。其次，请参考这篇帖子，其中包含更多有关各种方法的性能测试。 - ErikE
25
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

DateDiff(Day, 0, GetDate())与DateDiff(Day, '1900-01-01', GetDate())意义相同。

由于DateDiff返回整数，您将获得自1900年1月1日以来经过的天数。然后，您将该整数天数添加到1900年1月1日。其效果是去除时间部分。

我还应该提到，此方法适用于任何日期/时间部分（例如年、季度、月、日、小时、分钟和秒）。

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

最后一个，即秒的处理需要特殊处理。如果使用1900年1月1日，您将会收到错误提示。

两个日期时间列的差值导致运行时溢出。

可以通过使用不同的参考日期（如2000年1月1日）来避免此错误。

你确定这是最有效的方法吗？ - Matt Howells
我的测试表明，Matt的方法略微更快。我也觉得它更易读。 - Darrel Miller
2
在我看来，转换为浮点数是不好的实践，因为往返转换为日期时间并不可靠。请参阅此帖子以获取更多详细信息。 - ErikE
12
select cast(floor(cast(@datetime as float)) as datetime)

这段代码的原理是将日期时间转换为浮点型，得到自1900年1月1日至今的天数（包括一部分天数的小数部分）。取整后保留完整天数，再将其转换回日期时间格式。

我一直在使用相同的方法，而且效果非常好。就我所知，DATETIME 实际上是以 FLOAT 的形式存储的，因此该方法非常高效。我还记得在某个地方读到过它被 Microsoft 内部使用。不确定在 SS2008 中是否仍然是这样。 - kristof
4
SQL2008改进了很多（也很需要的）日期处理函数。将datetime转换为新的date类型只需进行简单的类型转换。 - Matt Howells
3
@kristof datetime并不以浮点数的形式进行物理存储，它由两个4字节的整数组成，第一个整数是自1900年1月1日以来的天数，第二个整数是从午夜开始的1/300秒滴答数。最后，我认为将datetime转换为浮点数是不好的做法，因为往返转换到datetime并不可靠。更多细节请参见此帖子。 - ErikE
听起来好像是以定点方式存储，而不是浮点方式内部存储。 - Reversed Engineer
9

在 SQL Server 2012 中使用：

select cast(getdate() as date)
2
虽然这是正确的，但问题是关于较旧的 MS SQL 2000 和 2005 版本，它们缺少“日期”数据类型。 - jpw
1
select cast(getdate()as varchar(11))as datetime
2
请注意，转换为varchar会更慢。请参阅此帖子获取更多详细信息。 - ErikE
0
1
为什么不测试一下dateadd/datediff呢？ - gbn
0
这不是真的。请查看链接的线程以获取更新，或者直接跳转到此帖子以获取更多详细信息。 - ErikE
0
0

要获取YYYY-MM-DD，请使用：

select convert(varchar(10), getdate(), 120)

编辑：哎呀，他想要一个DateTime而不是一个字符串。Oracle中TRUNC（）的等价物。你可以拿我发布的内容并转换回DateTime：

select convert(datetime, convert(varchar(10), getdate(), 120) , 120)
2
请注意，转换为varchar会更慢。请参阅此帖子获取更多详细信息。 - ErikE
-1

关于 SELECT CAST(CASt(GETDATE() AS int) AS DATETIME) 怎么样？

1
因为那不是 OP 所要求的内容？ - mbinette
我试过了，它可以向上舍入，这会导致不正确的结果。 - Pete Alvin
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接