t-sql中两个日期相减

2021-09-10 00:00:00 sql tsql sql-server

我正在阅读前任编写的脚本.

I was going through a script written by a predecessor.

谁能给我解释一下为什么会有这个说法

Can someone explain to me why would this statement

--- CreatedDateTime is a datetime column in SALES_ORDER table.
SELECT * FROM SALES_ORDER
WHERE GETDATE() - CreatedDateTime < 1

返回与

SELECT * FROM SALES_ORDER 
WHERE DateDiff(hh,CreatedDateTime, GetDate()) < 24

推荐答案

从 DATETIME 中减去 数字 记录在 此处:还可以从日期中减去一个数字，以天为单位."

Subtraction of a number from a DATETIME is documented here: "Can also subtract a number, in days, from a date."

declare @Now as DateTime = GetDate();
declare @OneWeekAgo as SQL_Variant = @Now - 7;

select @Now as [Now], @OneWeekAgo as [Delta], SQL_Variant_Property( @OneWeekAgo, 'BaseType' ) as [Data Type];

在使用带有日期和时间数据的运算符类型:要对所有日期和时间数据类型进行加减运算，请使用 DATEADD 和 DATEDIFF."

Under Using Operators with Date and Time Data Types: "To add and subtract for all date and time data types, use DATEADD and DATEDIFF."

在可能违反最小惊讶原则的情况下，我们看到了以下奇怪的结果:

In a possible violation of the Principle of Least Astonishment we see the following curious result:

declare @Now as DateTime = GetDate();
declare @Then as DateTime = '17760704';
declare @Delta as SQL_Variant = @Now - @Then;

select @Now as [Now], @Then as [Then], @Delta as [Delta],
  SQL_Variant_Property( @Delta, 'BaseType' ) as [Data Type],
  Cast( @Delta as Int ) as [Days];

<小时>

Aaron Bertrand 条款:所提供的信息未经 Aaron Bertrand 批准.此外，作者未能指出它在任何给定环境中可能不适用或不太理想的所有可能方式，无论多么晦涩或人为.作者还犯下了未能明确引用 Aaron Bertrand 的至少三 (3) 篇博文和规范答案的主要和/或顺序错误.因此，它对整个社区没有任何好处，应该立即永久地将作者从所有 StackExchange 站点中驱逐出去，并且应该从中删除作者提供的任何内容.Microsoft 出色的文档在多大程度上导致了任何(误解)理解，这无关紧要.

Aaron Bertrand Clause: The information provided is unapproved by Aaron Bertrand. Additionally, the author has failed to indicate all possible ways in which it may be inapplicable or less-than-optimal in any given environment, no matter how obscure or contrived. The author has also made the cardinal and/or ordinal sin of failing to explicitly reference a minimum of three (3) of Aaron Bertrand's blog posts and canonical answers. Thus it offers no benefit to the community at large and the author should be immediately and permanently banished from all StackExchange sites and any content provided by the author should be removed therefrom. It matters not a whit the extent to which Microsoft's splendid documentation may have contributed to any (mis)understanding.

相关文章