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

即使几年后,这仍然会频繁地获得额外的选票,因此我需要为现代版本的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天。无论您的日期时间是如何存储的,也不管您的区域设置是什么,它都会工作。

快车道:

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

这是因为datetime列存储为8字节的二进制值。将它们强制转换为浮点型,将它们强制转换为删除分数,当您将值强制转换回datetime时,值的时间部分将消失。它只是一点点的变化,没有复杂的逻辑,而且 非常 快。

请注意,这依赖于微软随时可以自由更改的实现细节,即使是在自动服务更新中。它也不是很轻便。在实践中,这种实现不太可能在短时间内改变,但是如果您选择使用它,那么注意到危险仍然很重要。现在我们有了一个约会的选择,这是很少必要的。

错误的方式:

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

错误的方法是转换为字符串、截断字符串并转换回日期时间。它是 错误的 ,有两个原因:1)它可能不适用于所有地区;2)它是实现这一目标最慢的方法……不仅仅是一点点,它就像一个数量级或两个比其他选项慢。

更新 这最近得到了一些投票,因此我想补充一点,自从我发布了这篇文章以来,我已经看到了一些非常可靠的证据,即SQL Server将优化“正确”方式和“快速”方式之间的性能差异,这意味着您现在应该更喜欢前者。

无论哪种情况,你都想 编写您的查询,以避免首先需要执行此操作 . 在数据库上做这项工作是非常罕见的。

在大多数地方,数据库已经是您的瓶颈。通常情况下,添加硬件以提高性能最昂贵的服务器和最难正确添加硬件的服务器(例如,您必须平衡磁盘和内存)。从技术和业务角度来看,向外扩展也是最困难的;从技术上讲,添加Web或应用程序服务器比添加数据库服务器要容易得多,即使这是错误的,您也不会为IIS或Apache支付每台服务器20000美元以上的许可证。

我试图指出的一点是,只要有可能,您就应该在应用程序级别完成这项工作。这个 只有 在SQL Server上截断日期时间应该是您需要按天分组的时候,即使这样,您也应该有一个额外的列设置为计算列,在插入/更新时维护,或者在应用程序逻辑中维护。让这个索引中断,CPU繁重的工作离开你的数据库。

仅限SQL Server 2008

CAST(@SomeDateTime AS Date) 

如果你想的话,把它转换回datetime。

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

为了得到更完整的答案,这里有一种工作方式,可以将数据部分截短,包括分钟(替换 GETDATE() 截短日期)。

这与公认的答案不同,因为您不仅可以使用 dd (天),但日期部分(参见 here ):

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

注意,在上面的表达式中, 0 是一年开始时的固定日期(1900-01-01)。如果需要截断到较小的部分,例如秒或毫秒,则需要采用一个更接近要截断日期的常量日期,以避免溢出。

我在网上找到的一个片段是:

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

在SQL 2005中,可以这样编写trunc_日期函数。

(1)

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

第一种方法要干净得多。它只使用3个方法调用,包括final cast(),不执行字符串连接,这是一个自动的加号。此外,这里也没有大型铸件。如果您可以想象日期/时间戳是可以表示的,那么从日期转换为数字和返回日期是一个相当容易的过程。

(2)

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

如果您担心微软的日期时间(2)或(3)的实现可能是好的。

(3)

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

第三,更详细的方法。这需要将日期拆分为年份、月份和日期部分,将它们组合成“YYYY/MM/DD”格式,然后将其转换回日期。这个方法涉及7个方法调用,包括final cast(),更不用说字符串连接。

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

选择cast(floor(cast(getdate()as float))作为datetime) 参考此: http://microsoftmiles.blogspot.com/2006/11/remove-time-from-datetime-in-sql-server.html

对于来这里寻找将日期时间字段截断为不到一整天的方法的人(例如每分钟),可以使用以下方法:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) + (FLOOR((CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 1440.0) + (3.0/86400000.0)) / 1440.0 AS DATETIME)

如果今天是 2010-11-26 14:54:43.123 然后这个会回来的 2010-11-26 14:54:00.000 .

若要将其停止的时间间隔更改为,请将1440.0替换为一天中的时间间隔数,例如:

24hrs          =   24.0  (for every hour)
24hrs / 0.5hrs =   48.0  (for every half hour)
24hrs / (1/60) = 1440.0  (for every minute)

(总是放一个 .0 最后隐式转换为浮点。)

对于那些想知道 (3.0/86400000) 在我的计算中,SQL Server 2005似乎不是从 FLOAT 到 DATETIME 准确地说,这会在铺地板前增加3毫秒。

此查询应为您提供相当于 trunc(sysdate) 在Oracle中。

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

希望这有帮助!

你也可以提取日期 using Substring 从datetime变量返回datetime将忽略时间部分。

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)

神谕:

TRUNC(SYSDATE, 'MONTH')

SQLServer:

DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField)

可以类似地用于从日期截断分钟或小时。

您可以这样做(SQL 2008):

declare@somedate=getdate()。

select @SomeDate

2009—05-28

trunc(adate,“dd”)将截断min、sec和hrs

SRC: http://www.techonthenet.com/oracle/functions/trunc_date.php