在 SQL Server 中截断日期的最佳方法是什么? [英] What is the best way to truncate a date in SQL Server?

问题描述

如果我有一个像 2010-03-01 17:34:12.018

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

What is the most efficient way to turn this into 2010-03-01 00:00:00.000?

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

As a secondary question, what is the best way to emulate Oracle's TRUNC function, which will allow you to truncate at Year, Quarter, Month, Week, Day, Hour, Minute, and Second boundaries?

推荐答案

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

To round to the nearest whole day, there are three approaches in wide use. The first one uses datediff to find the number of days since the 0 datetime. The 0 datetime corresponds to the 1st of January, 1900. By adding the day difference to the start date, you've rounded to a whole day;

select dateadd(d, 0, datediff(d, 0, getdate()))

第二种方法是基于文本的:它用varchar(10)截断文本描述，只留下日期部分:

The second method is text based: it truncates the text description with varchar(10), leaving only the date part:

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

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

The third method uses the fact that a datetime is really a floating point representing the number of days since 1900. So by rounding it to a whole number, for example using floor, you get the start of the day:

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

回答你的第二个问题，本周的开始比较棘手.一种方法是减去星期几:

To answer your second question, the start of the week is trickier. One way is to subtract the day-of-the-week:

select dateadd(dd, 1 - datepart(dw, getdate()), getdate())

这也会返回时间部分，因此您必须将其与时间剥离方法之一结合使用才能到达第一个日期.例如，将 @start_of_day 作为可读性变量:

This returns a time part too, so you'd have to combine it with one of the time-stripping methods to get to the first date. For example, with @start_of_day as a variable for readability:

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 年以来的差异"方法:

The start of the year, month, hour and minute still work with the "difference since 1900" approach:

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，作为参考日期:

Rounding by second requires a different approach, since the number of seconds since 0 gives an overflow. One way around that is using the start of the day, instead of 1900, as a reference date:

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:

To round by 5 minutes, adjust the minute rounding method. Take the quotient of the minute difference, for example using /5*5:

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

这也适用于四分之一小时和半小时.

This works for quarters and half hours as well.

这篇关于在 SQL Server 中截断日期的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！