从 SQL Server 中的日期时间字段中删除时间部分时,哪种方法可提供最佳性能?
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
或者
b) select cast(convert(char(11), getdate(), 113) as datetime)
第二种方法确实会发送更多字节,但这可能不如转换速度重要。
两者似乎也都非常快,但是在处理数十万或更多行时可能存在速度差异?
此外,是否有更好的方法来摆脱 SQL 中日期时间的时间部分?
严格来说,方法 a
是资源密集度最低的:
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
经手头有太多时间的人证明,在相同的总持续时间 100 万行中 CPU 密集度较低:Most efficient way in SQL Server to get a date from date+time?
我在其他地方也看到了类似的测试,结果也类似。
我更喜欢 DATEADD/DATEDIFF 因为:
varchar 受语言/日期格式问题的影响 示例:为什么我的 CASE 表达式是不确定的?
float 依赖于内部存储
它通过更改“0”基数扩展到每月的第一天、明天等
编辑,2011 年 10 月
对于 SQL Server 2008+,您可以转换为 date
即 CAST(getdate() AS date)
。或者只使用 date
数据类型,因此无需删除 time
。
编辑,2012 年 1 月
这是多么灵活的一个工作示例:Need to calculate by rounded time or date figure in sql server
编辑,2012 年 5 月
不要不加思索地在 WHERE 子句等中使用它:向列添加函数或 CAST 会使索引使用无效。在此处查看第 2 个Common SQL Programming Mistakes
现在,这确实有一个更高版本的 SQL Server 优化器版本正确管理 CAST 的示例,但通常这将是一个坏主意......
编辑,2018 年 9 月,用于 datetime2
DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'
select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)
在 SQL Server 2008 中,您可以使用:
CONVERT(DATE, getdate(), 101)
datetime
转换为 date
时,第三个参数与结果完全无关,因此您的解决方案有效地归结为 CONVERT(DATE,getdate())
,这已被多次建议。
CAST(GETDATE() AS DATE)
或严格的 ANSI CAST(CURRENT_TIMESTAMP AS DATE)
。留在第一个。
当然,这是一个旧线程,但要使其完整。
从 SQL 2008 开始,您可以使用 DATE 数据类型,因此您可以简单地执行以下操作:
SELECT CONVERT(DATE,GETDATE())
在 SQL Server 2008 中,有一个 DATE 日期类型(也是一个 TIME 数据类型)。
CAST(GetDate() as DATE)
或者
declare @Dt as DATE = GetDate()
SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)
...根据下面的评论,这不是一个好的解决方案。
我会删除这个答案,但我会把它留在这里作为反例,因为我认为评论者对为什么它不是一个好主意的解释仍然有用。
这是另一个答案,来自另一个 duplicate question:
SELECT CAST(CAST(getutcdate() - 0.50000004 AS int) AS datetime)
此幻数方法的执行速度比 DATEADD 方法稍快。 (看起来大约 10%)
几轮百万记录的 CPU 时间:
DATEADD MAGIC FLOAT
500 453
453 360
375 375
406 360
但请注意,这些数字可能无关紧要,因为它们已经非常快了。除非我有 100,000 或更多的记录集,否则我什至无法将 CPU 时间读取到零以上。
考虑到 DateAdd 用于此目的并且更强大的事实,我会说使用 DateAdd。
'12:00:00.003'
的可能性,但我认为这要好得多。
SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)
我很喜欢:
[date] = CONVERT(VARCHAR(10), GETDATE(), 120)
120
格式代码会将日期强制转换为 ISO 8601 标准:
'YYYY-MM-DD' or '2017-01-09'
在 dplyr (R
) 和 pandas (Python
) 中超级好用!
谨防!
方法 a) 和 b) 并不总是具有相同的输出!
select DATEADD(dd, DATEDIFF(dd, 0, '2013-12-31 23:59:59.999'), 0)
输出:2014-01-01 00:00:00.000
select cast(convert(char(11), '2013-12-31 23:59:59.999', 113) as datetime)
输出:2013-12-31 00:00:00.000
(在 MS SQL Server 2005 和 2008 R2 上测试)
编辑:根据亚当的评论,如果您从表中读取日期值,则不会发生这种情况,但如果您将日期值作为文字提供(例如:作为通过 ADO.NET 调用的存储过程的参数),则可能会发生这种情况。
DATETIME
列中。可用的最高值是 0.997 从:msdn.microsoft.com/en-us/library/ms187819.aspx 您会看到这些值被四舍五入到第 100 位为 0、3 或 7。OP 不会在他们的表中看到您的测试值。
看到这个问题:
How can I truncate a datetime in SQL Server?
无论您做什么,都不要使用字符串方法。这是你能做到的最糟糕的方式。
已经回答了,但我也要把它扔出去......这应该也很好,但它通过从浮点数中丢弃小数(存储时间)并只返回整个部分(即日期)来工作
CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)
我第二次找到这个解决方案... i grabbed this code off
CAST(round(cast(getdate()as real),0,1) AS datetime)
此方法不使用字符串函数。 Date
基本上是一种真实的数据类型,小数点前的数字是一天的一小部分。
我想这会比很多更快。
对我来说,下面的代码总是赢家:
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));
选择转换(字符(10),GetDate(),126)
首先去除插入/更新的时间。至于即时转换,没有什么比用户定义的函数可维护性更好的了:
select date_only(dd)
date_only
的实现可以是您喜欢的任何东西 - 现在它已被抽象出来,调用代码更加简洁。
WHERE DateAdd(DateDiff(Column)) = @DateValue
不会使用索引。另一方面,WHERE Column >= dbo.UDF(@DateValue) AND Column < dbo.UDF(@DateValue + 1)
是 SARGable。所以要小心你的表达方式。
我想你的意思是cast(floor(cast(getdate()as float))as datetime)
real 只有 32 位,可能会丢失一些信息
这是最快的cast(cast(getdate()+x-0.5 as int)as datetime)
...虽然只快 10% 左右(about 0.49 microseconds CPU vs. 0.58)
这是推荐的,并且在我刚才的测试中花费了相同的时间:DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
在 SQL 2008 中,SQL CLR 函数比使用 SQL 函数快大约 5 倍,分别为 1.35 微秒和 6.5 微秒,这表明 SQL CLR 函数与简单 SQL UDF 相比,函数调用开销要低得多。
根据我的测试,在 SQL 2005 中,SQL CLR 函数比这个慢速函数快 16 倍:
create function dateonly ( @dt datetime )
returns datetime
as
begin
return cast(floor(cast(@dt as float))as int)
end
select cast(cast my_datetime_field as date) as datetime)
怎么样?这会产生相同的日期,时间设置为 00:00,但避免了任何文本转换,也避免了任何显式数字舍入。
我认为,如果您严格遵守 TSQL
,这是缩短时间的最快方法:
select convert(datetime,convert(int,convert(float,[Modified])))
我发现这种截断方法比 DateAdd
方法快大约 5%。这可以很容易地修改为四舍五入到最近的一天,如下所示:
select convert(datetime,ROUND(convert(float,[Modified]),0))
在这里,我创建了一个函数来删除 SQL Server 的日期时间的某些部分。用法:
第一个参数是要剥离的日期时间。
第二个参数是一个字符: s:四舍五入到秒;删除毫秒 m:四舍五入到分钟;删除秒和毫秒 h:四舍五入到小时;删除分钟、秒和毫秒。 d:舍入到天;删除小时、分钟、秒和毫秒。
s:四舍五入到秒;删除毫秒
m:四舍五入到分钟;删除秒和毫秒
h:四舍五入到小时;删除分钟、秒和毫秒。
d:舍入到天;删除小时、分钟、秒和毫秒。
返回新的日期时间
create function dbo.uf_RoundDateTime(@dt as datetime, @part as char) returns datetime as begin if CHARINDEX( @part, 'smhd',0) = 0 return @dt; return cast( Case @part when 's' then convert(varchar(19), @dt, 126) when 'm' then convert(varchar(17), @dt, 126) + '00' when 'h' then convert(varchar(14), @dt, 126) + '00:00' when 'd' then convert(varchar(14), @dt, 112) end as datetime ) end
以防万一有人在这里寻找 Sybase 版本,因为上面的几个版本都不起作用
CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME)
在 Adaptive Server 15.7 上运行的 I SQL v11 中测试
cast
:对于 SQL Server 2008+,您可以 CAST 到日期。或者只是使用日期,所以没有时间删除。
datetime
转换为 date
时为 CONVERT 指定第三个参数是没有意义的:它们都没有固有的格式。
如果可能的话,对于像这样的特殊事情,我喜欢使用 CLR 函数。
在这种情况下:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime DateOnly(SqlDateTime input)
{
if (!input.IsNull)
{
SqlDateTime dt = new SqlDateTime(input.Value.Year, input.Value.Month, input.Value.Day, 0, 0, 0);
return dt;
}
else
return SqlDateTime.Null;
}
就我个人而言,如果处理 SQL Server 2005(或更低版本),我几乎总是使用 User Defined functions,但是,应该注意使用 UDF 有一些特定的缺点,尤其是在将它们应用于 WHERE 子句时(见下文和对此答案的评论以获取更多详细信息)。如果使用 SQL Server 2008(或更高版本) - 请参见下文。
事实上,对于我创建的大多数数据库,我在开始时就添加了这些 UDF,因为我知道有 99% 的机会我迟早会需要它们。
我为“仅日期”和“仅时间”创建了一个(尽管“仅日期”是迄今为止最常用的两个)。
以下是各种与日期相关的 UDF 的一些链接:
Essential SQL Server Date, Time and DateTime Functions
Get Date Only Function
最后一个链接显示不少于 3 种不同的方法来获取日期时间字段的仅日期部分,并提到了每种方法的一些优缺点。
如果使用 UDF,应注意应尽量避免将 UDF 用作查询中 WHERE 子句的一部分,因为这将极大地影响查询的性能。主要原因是在 WHERE 子句中使用 UDF 会将该子句呈现为 non-sargable,这意味着 SQL Server 不能再使用带有该子句的索引来提高查询执行速度。参考我自己对 UDF 的使用,我将经常在 WHERE 子句中使用“原始”日期列,但将 UDF 应用于 SELECTed 列。这样,UDF 仅应用于过滤的结果集,而不是作为过滤器的一部分的表的每一行。
当然,绝对最佳的方法是使用 SQL Server 2008(或更高版本)并分离出您的 dates and times,因为 SQL Server 数据库引擎会在本地提供单独的日期和时间组件, 并且可以有效地独立查询这些,而不需要 UDF 或其他机制来从复合日期时间类型中提取日期或时间部分。
WHERE DateColumn >= {TimeTruncatingExpression}(@DateValue) AND DateColumn < {TimeTruncatingExpression}(@DateValue + 1)
。我觉得我不得不说点什么,因为您说“我几乎总是使用 UDF”并没有解释任何缺点,也没有解释进行仅日期查询 SARGable 的方法。
我会使用:
CAST
(
CAST(YEAR(DATEFIELD) as varchar(4)) + '/' CAST(MM(DATEFIELD) as varchar(2)) + '/' CAST(DD(DATEFIELD) as varchar(2)) as datetime
)
因此有效地从您已有的日期字段创建一个新字段。
datetime
值中提取位、将它们转换为字符串、将它们连接在一起并最终将结果转换回 datetime
比直接计算原始 datetime
(DATEADD
/ DATEDIFF
方法)?
MM
和 DD
? SQL Server 中没有这样的函数。
不定期副业成功案例分享
DATE
数据时间在 dateadd、datediff 和与其他日期/时间数据类型交互等方面的限制令人讨厌。对于这些情况,DATEADD()
方法是王道。0218
而不是2018
作为年份,并且您语句的DATEDIFF
部分引发异常The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range datetime value
Try:select DATEDIFF(dd, 0, convert(datetime2(0), '0218-09-12', 120))
SELECT DATEDIFF(dd, '19000101', convert(datetime2(0), '0218-09-12', 120))