删除SQL Server中datetime时间部分的最佳方法-Java 学习之路

457

从SQL Server中的datetime字段中删除时间部分时，哪种方法提供了最佳性能？

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

要么

b) select cast(convert(char(11), getdate(), 113) as datetime)

第二种方法确实发送了更多的字节，但这可能没有转换速度那么重要 .

两者似乎都非常快，但在处理数十万或更多行时速度可能会有所不同？

另外，是否有可能有更好的方法来摆脱SQL中日期时间的时间部分？

23 回答

2
对我来说，下面的代码永远是赢家：
```
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));
```
回复于 2024-04-28T02:44:18+08:00
8
严格来说，方法 a 是资源最少的：
```
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
```
事实证明，对于相同的总持续时间而言CPU占用的密集程度是一百万行，因为他们手上有太多时间：Most efficient way in SQL Server to get date from date+time?

我在其他地方也看到了类似的测试结果 .

我更喜欢DATEADD / DATEDIFF，因为：
- varchar受语言/日期格式问题的影响
  示例：Why is my CASE expression non-deterministic?
- float依赖于内部存储
- 它通过改变"0" base延伸到一月的第一天，明天等
Edit, Oct 2011

对于SQL Server 2008，您可以CAST到 date . 或者只是使用 date 所以没时间删除 .

Edit, Jan 2012

这是一个多么灵活的有效例子：Need to calculate by rounded time or date figure in sql server

Edit, May 2012

不要在WHERE子句等中使用它而不考虑：向列添加函数或CAST会使索引使用无效 . 见这里的第2号：http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

现在，这确实有一个后来SQL Server优化器版本的例子，管理CAST到目前为止正确，但通常这将是一个坏主意...

Edit, Sep 2018, for 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)
```
回复于 2024-04-28T02:44:18+08:00
2
在SQL Server 2008中，您可以使用：
```
CONVERT(DATE, getdate(), 101)
```
回复于 2024-04-28T02:44:18+08:00
5
当然，这是一个旧线程，但要完成它 .

从SQL 2008开始，您可以使用DATE数据类型，这样您就可以执行以下操作：
```
SELECT CONVERT(DATE,GETDATE())
```
回复于 2024-04-28T02:44:18+08:00

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

回复于 2024-04-28T02:44:18+08:00

1
在SQL Server 2008中，存在DATE日期类型（也是TIME数据类型） .
```
CAST(GetDate() as DATE)
```
要么
```
declare @Dt as DATE = GetDate()
```
回复于 2024-04-28T02:44:18+08:00
2
这是另一个答案，来自另一个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 .
回复于 2024-04-28T02:44:18+08:00

SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)

回复于 2024-04-28T02:44:18+08:00

0
首先删除插入/更新的时间 . 至于即时转换，没有什么可以击败用户定义的功能maintanability：
```
select date_only(dd)
```
date_only 的实现可以是你喜欢的任何东西 - 现在它被抽象掉了，调用代码要清晰得多 .
回复于 2024-04-28T02:44:18+08:00
1

看到这个问题：
How can I truncate a datetime in SQL Server?

不管你做什么， don't use the string method . 这是你能做到的最糟糕的方式 .

回复于 2024-04-28T02:44:18+08:00
497
已经回答但是生病也把它扔出去了......这个也很好地预先形成但它的工作方式是从浮动中丢弃小数（存储时间）并仅返回整个部分（这是日期）
```
CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)
```
我第二次找到这个解决方案... i grabbed this code off
回复于 2024-04-28T02:44:18+08:00
2
```
CAST(round(cast(getdate()as real),0,1) AS datetime)
```
此方法不使用字符串函数 . Date 基本上是一个真正的数据类型，小数点前的数字是一天的一小部分 .

我想这会比很多快 .
回复于 2024-04-28T02:44:18+08:00
2
谨防！

方法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上测试过）

编辑：根据Adam的评论，如果您从表中读取日期值，则不会发生这种情况，但如果您将日期值作为文字提供，则会发生这种情况（例如：作为通过ADO.NET调用的存储过程的参数） .
回复于 2024-04-28T02:44:18+08:00
1

选择CONVERT（char（10），GetDate（），126）

回复于 2024-04-28T02:44:18+08:00
2
我很喜欢：
```
[date] = CONVERT(VARCHAR(10), GETDATE(), 120)
```
120 格式代码会将日期强制转换为ISO 8601标准：
```
'YYYY-MM-DD' or '2017-01-09'
```
在dplyr（ R ）和pandas（ Python ）中超级好用！
回复于 2024-04-28T02:44:18+08:00
53
我想你的意思是 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
```
回复于 2024-04-28T02:44:18+08:00
36

select cast(cast my_datetime_field as date) as datetime) 怎么样？这导致相同的日期，时间设置为00:00，但避免任何文本转换，并避免任何显式数字舍入 .

回复于 2024-04-28T02:44:18+08:00
1
我认为，如果你严格遵守 TSQL ，这是截断时间的最快方法：
```
select convert(datetime,convert(int,convert(float,[Modified])))
```
我发现这种截断方法比 DateAdd 方法快5％左右 . 这可以很容易地修改为舍入到最近的一天，如下所示：
```
select convert(datetime,ROUND(convert(float,[Modified]),0))
```
回复于 2024-04-28T02:44:18+08:00
0
在这里，我创建了一个函数来删除SQL Server的日期时间的某些部分 . 用法：
- 第一个参数是要剥离的日期时间 .
- 第二个参数是一个字符：
- s：回合到秒;删除毫秒
- 米：几分钟到几分钟;删除秒和毫秒
- 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
回复于 2024-04-28T02:44:18+08:00
2
以防万一有人在这里寻找Sybase版本，因为上面的几个版本都不起作用
```
CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME)
```
- 在Adaptive Server 15.7上运行的I SQL v11中进行了测试
回复于 2024-04-28T02:44:18+08:00

如果可能的话，对于像这样的特殊事情，我喜欢使用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;
    }

回复于 2024-04-28T02:44:18+08:00

-3

我个人，如果处理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 's, I' ll的使用经常使用WHERE子句中的"raw" date列，但是将UDF应用于SELECTed列 . 这样，UDF仅应用于过滤的结果集，而不是作为过滤器的一部分应用于表的每一行 .

当然，绝对最好的方法是使用SQL Server 2008（或更高版本）并分离出dates and times，因为SQL Server数据库引擎本身提供了各自的日期和时间组件，并且可以独立地有效地查询这些组件而无需需要UDF或其他机制来从复合日期时间类型中提取日期或时间部分 .

回复于 2024-04-28T02:44:18+08:00

我会用：

CAST
(
CAST(YEAR(DATEFIELD) as varchar(4)) + '/' CAST(MM(DATEFIELD) as varchar(2)) + '/' CAST(DD(DATEFIELD) as varchar(2)) as datetime
)

从而有效地从您已有的日期字段创建一个新字段 .

回复于 2024-04-28T02:44:18+08:00

删除SQL Server中datetime时间部分的最佳方法

23 回答

相关问题