logo标签列表

什么是在SQL Server中截断日期的最佳方法？

sql-servert-sql
24

如果我有一个日期值，例如2010-03-01 17:34:12.018

最有效的方法是将其转换为2010-03-01 00:00:00.000？

作为一个次要问题，模拟Oracle的TRUNC函数的最佳方法是什么？该函数允许您在年、季度、月、周、日、小时、分钟和秒边界处进行截断。

这里有一个类似的问题，并且有一个很好的解释：https://dev59.com/DHNA5IYBdhLWcg3wh-cC - Oleks
7个回答
40

要将日期四舍五入到最接近的整天，有三种广泛使用的方法。第一种方法使用datediff查找自0日期时间以来的天数。 0日期时间对应于1900年1月1日。通过将天数差添加到起始日期，您已经将其四舍五入到整天；

select dateadd(d, 0, datediff(d, 0, getdate()))
第二种方法是基于文本的：它使用varchar(10)截断文本描述，只留下日期部分。
The second method is text-based: it uses varchar(10) to truncate the text description, leaving only the date part.
select convert(varchar(10),getdate(),111)

第三种方法利用了一个事实，即 datetime 实际上是一个浮点数，表示自1900年以来的天数。 因此通过将其四舍五入为整数，例如使用 floor，您可以获得一天的开始：

select cast(floor(cast(getdate() as float)) as datetime)
为了回答你的第二个问题，一周的开始比较棘手。其中一种方法是减去这一周的星期几：
select dateadd(dd, 1 - datepart(dw, getdate()), getdate())

这个方法也会返回时间部分，所以你需要将它与去除时间信息的方法结合使用才能得到第一个日期。例如，使用变量@start_of_day来提高代码可读性：

declare @start_of_day datetime
set @start_of_day = cast(floor(cast(getdate() as float)) as datetime)
select dateadd(dd, 1 - datepart(dw, @start_of_day), @start_of_day)

年、月、小时和分钟的起始时间仍然使用“自1900年以来的差异”方法：

select dateadd(yy, datediff(yy, 0, getdate()), 0)
select dateadd(m, datediff(m, 0, getdate()), 0)
select dateadd(hh, datediff(hh, 0, getdate()), 0)
select dateadd(mi, datediff(mi, 0, getdate()), 0)

按秒舍入需要采用不同的方法，因为从0开始计算的秒数会溢出。解决这个问题的一个方法是使用一天的开始作为参考日期，而不是1900年：

declare @start_of_day datetime
set @start_of_day = cast(floor(cast(getdate() as float)) as datetime)
select dateadd(s, datediff(s, @start_of_day, getdate()), @start_of_day)

要按照5分钟取整，需要调整分钟的取整方法。取分钟差商数的方式进行取整，例如使用/5*5：

select dateadd(mi, datediff(mi,0,getdate())/5*5, 0)

这也适用于季度和半小时。

哪个会表现得最好？ - John Gietzen
据我所知，性能差异太小以至于无法测量。 - Andomar
这也是我想知道的！！！ 我在我的代码中使用第二个，但我很好奇是否有更快的方法来做到这一点。 第一个是两个日期函数和第三个函数调用，因此至少有三个解析操作，而第二个是将单个强制转换为字符串并应用格式（但其中存在问题，库中会发生什么？）... - jcolebrand
第一个运行时间只需要四分之一。 - Anthony Faull
4
我的好奇心促使我对包含一百万个日期时间行的 SQL 2008 进行了一些测试。三种方法（转换为浮点数、datediff、转换为日期）的性能都非常接近。转换为浮点数是最快的，其次是转换为日期，然后是 datediff 方法，虽然速度略慢但仍然很快。 - Thomas
1
我刚刚注意到，在datetime2数据类型中，浮点数转换方法无法使用。因此，如果将来表格更改为datetime2，则选择此方法可能会使您的代码不可移植。 - Nap
19
如果你正在使用SQL Server 2008+，你可以像这样使用Date数据类型：
select cast(getdate() as date)
如果您仍需要将值作为DateTime数据类型处理，可以这样做：
select cast(cast(getdate() as date) as datetime)

在所有版本的SQL Server上都应该有效的方法是：

select cast(floor(cast(getdate() as float)) as datetime)
我敢打赌这是最快的，完全避免了算术运算。 - John Gietzen
3

尝试：

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

更新：关于第二个问题的回答： 多年来，您可以使用我回答的稍微修改过的版本：

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

季度：

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

等等，我查看了一下，到分钟的时间是可以的。但是在秒钟方面，我收到了溢出消息：

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

再更新一下:请看同一个问题的以下答案

3

虽然有些晚，但这段代码将会产生与问题帖子中请求的完全相同的结果。我认为它比使用dateadd更直观，但这只是我的偏好。

declare @SomeDate datetime = '2010-03-01 17:34:12.018'
SELECT 
 DATEFROMPARTS(
     YEAR(@SomeDate)
    ,MONTH(@SomeDate)
    ,'01'
    ) AS CUR_DATE_FROM_PARTS
,DATETIMEFROMPARTS(
     YEAR(@SomeDate)                     
    ,MONTH(@SomeDate)                
    ,'01' --DAY(@SomeDate)                   
    ,'00' --DATEPART(HOUR,@SomeDate)         
    ,'00' --DATEPART(MINUTE,@SomeDate)       
    ,'00' --DATEPART(SECOND,@SomeDate)       
    ,'00' --DATEPART(MILLISECOND,@SomeDate) 
    ) AS CUR_DATETIME_FROM_PARTS
,@SomeDate                         AS CUR_DATETIME
,YEAR(@SomeDate)                   AS CUR_YEAR
,MONTH(@SomeDate)                  AS CUR_MONTH
,DAY(@SomeDate)                    AS CUR_DAY
,DATEPART(HOUR,@SomeDate)          AS CUR_HOUR
,DATEPART(MINUTE,@SomeDate)        AS CUR_MINUTE
,DATEPART(SECOND,@SomeDate)        AS CUR_SECOND
,DATEPART(MILLISECOND,@SomeDate)   AS CUR_MILLISECOND
FROM Your_Table

截断日期: 2010年03月01日

截断日期时间: 2010年03月01日 00时00分00秒000毫秒

日期时间: 2010年03月01日 17时34分12秒017毫秒

1
SQL Server 2022 引入了 DATETRUNC() 函数，该函数类似于早已存在的 Oracle TRUNC() 函数。它也可在 Azure SQL 中使用。
语法： DATETRUNC(datepart, date) 
SELECT
  1 AS SortKey,
  '(none)' AS [Trunc date part],
  'Right now' AS Result,
  SYSDATETIME() AS Value
UNION SELECT 2, 'YEAR', 'Beginning of this year', DATETRUNC(YEAR, SYSDATETIME())
UNION SELECT 3, 'MONTH', 'Beginning of this month', DATETRUNC(MONTH, SYSDATETIME())
UNION SELECT 4, 'DAY', 'Zero hour today', DATETRUNC(DAY, SYSDATETIME())
UNION SELECT 5, 'HOUR', 'Beginning of this hour', DATETRUNC(HOUR, SYSDATETIME())
UNION SELECT 6, 'MINUTE', 'Beginning of this minute', DATETRUNC(MINUTE, SYSDATETIME())
UNION SELECT 7, 'SECOND', 'Beginning of this second', DATETRUNC(SECOND, SYSDATETIME())
ORDER BY SortKey

2023年6月16日晚上11:30左右的运行结果如下：
排序键 截断日期部分 结果 数值
1 （无） 现在 2023-06-17 03:30:04.4097820
2 年 今年初 2023-01-01 00:00:00.0000000
3 月 本月初 2023-06-01 00:00:00.0000000
4 日 今天零点 2023-06-17 00:00:00.0000000
5 小时 本小时初 2023-06-17 03:00:00.0000000
6 分钟 本分钟初 2023-06-17 03:30:00.0000000
7 秒钟 本秒钟初 2023-06-17 03:30:04.0000000

小提琴

0

如果你想以一种独立于 SET DATEFIRST 的方式将日期截断到周的开始，你可以这样做：

--change a date backwards to nearest Monday
DATEADD(DAY, (DATEPART(dw, '2001-01-01') - DATEPART(dw, YOUR_COLUMN)-7)%7, YOUR_COLUMN)

分解一下：

  • DATEPART(dw, '2001-01-01') - DATEPART(dw, YOUR_COLUMN): 通过已知星期一（2001年1月1日是星期一）的dw减去你的日期的dw，有效地给出了“你的日期与星期一之间的天数差”或“我们需要向后滚动多少天才能使你的日期成为星期一”

  • ( ... -7)%7：我们从中减去7并对结果取模。

    • 我们这样做是因为如果例如DATEFIRST是7（星期日），而你的日期列是星期日，则你的dw是1，而你已知的星期一是2。
    • 这意味着dw_for_known_monday - dw_for_your_date的结果是+1而不是-6
    • 这将意味着DATEADD会将你的日期向前滚动到下一个星期一，而不是向后滚动到上一个星期一
    • 如果我们从中减去7，那么结果肯定是负数，但是我们不希望你的星期一（与已知星期一相差0天）最终进行DATEADD的是-7，因此我们对结果取模。
    • 这将任何问题的-7转换为0，这意味着你的DATEADD只会有一个介于-6和0之间的“天数”参数

  • DATEADD(DAY, (DATEPART(dw, '2001-01-01') - DATEPART(dw, YOUR_COLUMN)-7)%7, YOUR_COLUMN)：然后我们从上一步中得到的（负数）天数进行DATEADD

如果想将日期往前推到上个星期日，需将日期2001-01-01更改为一个已知的星期日日期（如2000-12-31），以此类推。

0

不确定这是否是最有效的方法，但我喜欢使用以下简单的方法，其中@SomeDate是您的字段。

Concat(Year(@SomeDate), '-', Month(@SomeDate), '-', '01')
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接