如何在SQL Server中截断日期时间？

Question

在SQL Server中，截断日期时间值（以删除小时、分钟和秒）的最佳方法是什么？

例如：

declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)

-----------------------
2009-05-28 00:00:00.000

- Julio César

Answer 1

这个问题仍然经常收到额外的投票，即使是几年后，因此我需要为现代版本的Sql Server更新它。对于Sql Server 2008及更高版本，这很简单：

cast(getdate() as Date)

请注意，在底部附近的最后三段仍然适用，你经常需要退一步找到避免首次转换的方法。

但是，还有其他方法可以做到这一点。以下是最常见的方法。

正确的方法（自 Sql Server 2008 新增）

cast(getdate() as Date)

正确的方式（旧）

dateadd(dd, datediff(dd, 0, getdate()), 0)

这个方法现在已经有点老了，但仍然值得知道，因为它也可以轻松适应其他时间点，比如月份、分钟、小时或年份的第一刻。

这种正确的方式使用了文档化的函数，这些函数是 ANSI 标准的一部分，并且保证可以工作，但速度可能会稍慢。它的原理是找到从第 0 天到当前日期有多少天，并将这么多天加回到第 0 天。无论你的 datetime 如何存储，无论你的语言环境如何，它都能正常工作。

快速的方式

cast(floor(cast(getdate() as float)) as datetime)

这个方法有效是因为datetime列被存储为8字节的二进制值。将它们转换为float，使用floor函数去除小数部分，再将它们转换回datetime时，时间部分就会消失。这只是简单的位移操作，没有复杂的逻辑，并且非常快速。

需要注意的是，这依赖于微软随时可以更改的实现细节，甚至在自动服务更新中也可能发生变化。而且它也不太可移植。实际上，这种实现很可能在短期内不会改变，但如果选择使用它，仍然要意识到存在的风险。现在我们有了将其转换为日期的选项，很少需要使用这种方法。

错误的方法

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

错误的方法是将日期转换为字符串，截断字符串，然后再转换回日期。这种方法有两个问题：

1. 它可能在所有地区都不起作用
2. 这是最慢的方法之一... 不仅仅是稍微慢一点；它比其他选项慢一个数量级或两个数量级。

---

更新 最近这篇文章有些赞，因此我想补充一点，自从我发表这篇文章以来，我看到了一些相当坚实的证据表明 Sql Server 将优化掉“正确”的方式和“快速”的方式之间的性能差异，这意味着你现在应该更倾向于前者。

无论哪种情况，你都要编写查询以避免首先需要这样做。 很少需要在数据库上进行这项工作。

在大多数地方，数据库已经成为你的瓶颈。通常情况下，服务器是最昂贵的硬件添加和最难正确增加的（例如，你必须平衡磁盘和内存）。从技术和商业角度来看，它也是最难扩展的；相比之下，添加 Web 或应用服务器要容易得多，即使这是错误的，你也不需要支付每个服务器许可证 20,000 美元以上的 IIS 或 Apache。

我想说的是，尽可能应该在应用程序层面完成这项工作。唯一需要在 Sql Server 上截断日期时间的情况是当您需要按天进行分组时，即使这样，您也应该设置一个额外的计算列，在插入/更新时维护，或在应用程序逻辑中维护。将这种破坏索引、占用 CPU 的工作从数据库中移除。

- Joel Coehoorn

---

根据我刚刚进行的基准测试，对于 SQL 2008 来说，“快速方式”仍然是最快的方式。 - Sam Saffron

FYI: https://dev59.com/Q3M_5IYBdhLWcg3w43pt 和 https://dev59.com/TXVC5IYBdhLWcg3w9GPM。dateadd/datediff "胜出"。当只涉及单个变量时，这并不重要，我们希望您已经通过计算列或对一百万行以上的数据进行了优化 :) - gbn

这个“正确”的方法只是偶然起作用了。它的写法好像DateAdd的语法是（间隔，日期，增量），但实际上不是这样。它应该是（间隔，增量，日期）。当我试图把日期截断到月初时，我偶然发现了这一点：选择DATEADD(m, 0, DATEDIFF(m, 0, GETDATE()))是行不通的，但选择DATEADD(m, DATEDIFF(m,0,GETDATE()), 0)则可以。至少在我使用2008R2时是这样。 - Kelly Cline

它们都适用于日期时间列。这里的 getdate() 是您可能拥有的任何日期时间源的替代品。 - Joel Coehoorn

回复：“将这个CPU密集型工作从你的数据库中移除。” 但是它真的需要那么多工作吗？我的手机可以每秒渲染数百万个纹理映射三角形的非常复杂的3D环境。在SQL Server中删除日期中的时间是否真的会导致你可以测量到的问题（即使对于数百万行）？ - NickG

Answer 2

仅适用于SQL Server 2008

CAST(@SomeDateTime AS Date) 

如果需要的话，将其转换回datetime类型。

CAST(CAST(@SomeDateTime AS Date) As datetime)

- DJ.

---

好点：我仍在使用2005，所以对于2008来说，这可能是新的“正确”方式，甚至可以匹配“快速”方式的性能。 - Joel Coehoorn

这种新方法的性能甚至比“快速”方法还要更快。 - ErikE

Answer 3

为了给出一个更完整的答案，以下是一种可行的方法来将日期截断到包括分钟在内的任何时间部分（将GETDATE()替换为要截断的日期）。

不同于已接受答案的是，您可以使用除了dd（天）以外的任何时间部分（详见此处）：

dateadd(minute, datediff(minute, 0, GETDATE()), 0)

请注意，上述表达式中的0是一年的开始日期（1900-01-01）的常量。如果您需要将时间截断为更小的部分，例如秒或毫秒，您需要选择一个更接近要截断的日期的常量日期，以避免溢出。

- Lucero

---

这非常有帮助。我四处寻找一种方法来截断日期时间，使其低于整天。 - Michael

只是让互联网知道，您不必局限于完整的日期部分周期。以下是一个使用整数除法的15分钟间隔示例：dateadd(minute, datediff(minute, 0, GETDATE()) / 15 * 15, 0)。 - Michael

Answer 4

我在需要这样做时在网上找到的片段如下：

我在需要这样做时在网上找到的片段如下：

 dateadd(dd,0, datediff(dd,0, YOURDATE))
 e.g.
 dateadd(dd,0, datediff(dd,0, getDate()))

- Tom Ritter

---

我现在用的是2005版，但我记得2008版有一些新功能可以做到这个？ - KM.

不错！我本来会拆分日期部分并使用字符串处理将它们重新组合。可能与此无关，但 SQL2008 有一个纯日期数据类型而没有时间元素。 - flytzen

请注意，您的DateAdd操作数混淆了，应该是DateAdd(dd, DateDiff(...), 0)。如果不小心，这可能会对您造成影响。 - ErikE

Answer 5

CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)

- Dean

Answer 6

当使用分析时，您可能经常需要截断日期\时间。因此，我制作了一个小函数来帮助解决这个问题：

CREATE FUNCTION TRUNC_DATE
(
    @datetime datetime, -- datetime to be truncated
    @level VARCHAR(10)  -- truncation level: year, month, day, hour and minute
)
RETURNS DATETIME
AS
BEGIN

    IF (UPPER(@level) = 'YEAR')
       RETURN DATEADD(YEAR,   DATEDIFF(YEAR, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'MONTH')
        RETURN DATEADD(MONTH,   DATEDIFF(MONTH, 0, @datetime), 0)
    ELSE IF(UPPER(@level) = 'DAY')
       RETURN DATEADD(DAY,   DATEDIFF(DAY, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'HOUR')
       RETURN DATEADD(HOUR,   DATEDIFF(HOUR, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'MINUTE')
       RETURN DATEADD(MINUTE,   DATEDIFF(MINUTE, 0, @datetime), 0)

    RETURN @datetime
END
GO

为了评估函数（将GETDATE()更改为您的列）：

SELECT DBO.TRUNC_DATE(GETDATE(), 'YEAR')   YEAR;
SELECT DBO.TRUNC_DATE(GETDATE(), 'MONTH')  YEAR_MONTH;
SELECT DBO.TRUNC_DATE(GETDATE(), 'DAY')    YEAR_MONTH_DAY;
SELECT DBO.TRUNC_DATE(GETDATE(), 'HOUR')   YEAR_MONTH_DAY_HOUR;
SELECT DBO.TRUNC_DATE(GETDATE(), 'MINUTE') YEAR_MONTH_DAY_HOUR_MINUTE;

输出：

- Daniel Bonetti

Answer 7

SQL Server 2022有DATETRUNC函数

DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);

Year        2021-01-01 00:00:00.0000000
Quarter     2021-10-01 00:00:00.0000000
Month       2021-12-01 00:00:00.0000000
Week        2021-12-05 00:00:00.0000000
Iso_week    2021-12-06 00:00:00.0000000
DayOfYear   2021-12-08 00:00:00.0000000
Day         2021-12-08 00:00:00.0000000
Hour        2021-12-08 11:00:00.0000000
Minute      2021-12-08 11:30:00.0000000
Second      2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560

https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql?view=sql-server-ver16

- Imran Qadir Baksh - Baloch

---

OP在他们的请求中特别提到了2008年。 - Jeff Moden

@JeffModen 或许是吧，但当旧版本不再受支持时，更新问题以获取新功能通常是必要的。你不会期望一个只声明 C# v2 的问题仅保留旧答案。而这篇文章几乎可以成为此类问题的指南。 - Charlieface

@Charlieface - 啊...好的。我明白你的意思了。谢谢你的解释。 - Jeff Moden

Answer 8

在SQL 2005中，您的trunc_date函数可以编写如下。

1. 第一种方法更加简洁。它仅使用了3个方法调用，包括最终的CAST()，并且不执行任何字符串连接操作，这是一个自动加分项。此外，这里没有大量的类型转换。如果你能想象日期/时间戳可以被表示，那么从日期到数字再到日期的转换过程是相当容易的。

   CREATE FUNCTION trunc_date(@date DATETIME)
   RETURNS DATETIME
   AS
   BEGIN
       CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME)
   END
   

2. 如果你关心Microsoft对DATETIME的实现，那么(2)或(3)可能是可以接受的。

   CREATE FUNCTION trunc_date(@date DATETIME)
   RETURNS DATETIME
   AS
   BEGIN
         SELECT CONVERT(varchar, @date,112)
   END
   

3. 第三种方法比较冗长。这需要将日期分解为年、月和日部分，以"yyyy/mm/dd"格式组合起来，然后将其转换回日期。这种方法涉及7个方法调用，包括最终的CAST()，更不用说字符串连接了。

   CREATE FUNCTION trunc_date(@date DATETIME)
   RETURNS DATETIME
   AS
   BEGIN
   SELECT CAST((STR( YEAR( @date ) ) + '/' +STR( MONTH( @date ) ) + '/' +STR( DAY(@date ) )
   ) AS DATETIME
   END
   

- AlejandroR

Answer 9

你也可以使用 Substring 从日期时间变量中提取日期，将其转换回日期时间类型会忽略时间部分。

declare @SomeDate datetime = '2009-05-28 16:30:22'
SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime) 

此外，您可以访问datetime变量的部分并将它们合并为截断日期构造，例如：

SELECT cast(DATENAME(year, @Somedate) + '-' + 
       Convert(varchar(2),DATEPART(month, @Somedate)) + '-' +
       DATENAME(day, @Somedate) 
       as datetime)

- NeverHopeless

Answer 10

这个查询应该给你在 Oracle 中等同于 trunc(sysdate) 的结果。

SELECT  * 
FROM    your_table
WHERE   CONVERT(varchar(12), your_column_name, 101)
      = CONVERT(varchar(12), GETDATE(), 101)

希望这能帮到你！

- Sandeep Gaadhe