Subtraction of two dates in t-sql

问题:

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

returns the same result as

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

回答1:

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];

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 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.

回答2:

Both boolean expressions evaluate to the same thing. Either it's 24 hours old or 1 day old. Incidentally both of them exclude the usage of an index on the CreatedDateTime column. If you have an index on this column and would like to increase the likelihood of it being used then you would write it more like this:

    SELECT * FROM SALES_ORDER
    WHERE CreatedDateTime > GETDATE() - 1

or this (less than one day old):

    SELECT * FROM SALES_ORDER
    WHERE CreatedDateTime > DateAdd(dd,-1,GetDate())

or this (less than 86400000 milliseconds old old):

    SELECT * FROM SALES_ORDER
    WHERE CreatedDateTime > DateAdd(ms,-86400000,GetDate())