在 SQL 中减去两个日期并得到结果的天数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14119277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subtract two dates in SQL and get days of the result
提问by Cute Bear
Select I.Fee
From Item I
WHERE GETDATE() - I.DateCreated < 365 days
How can I subtract two days? Result should be days. Ex: 365 days. 500 days.. etc...
如何减去两天？结果应该是几天。例如：365 天。500天……等等……
回答by Habib
回答by John Woo
回答by MarkD
EDIT: It seems I was wrong about the performance on the code example. The best performer is whichever snippet runs second in the posted case. This demonstrates what I was trying to explain, and the time differences are not as dramatic:
编辑：似乎我对代码示例的性能有误。表现最好的是在发布的案例中运行第二的片段。这证明了我试图解释的内容，并且时间差异并不那么显着：
----------------------------------
-- Monitor time differences
----------------------------------
CREATE CLUSTERED INDEX dtIDX ON #ArbDates (MyDate)
DECLARE @Stopwatch DATETIME
SET @Stopwatch = GETDATE()
-- SARGABLE
SELECT *
FROM #ArbDates
WHERE MyDate > DATEADD(DAY, -364, '2010-01-01')
PRINT DATEDIFF(MS, @Stopwatch, GETDATE())
SET @Stopwatch = GETDATE()
-- NOT SARGABLE
SELECT *
FROM #ArbDates
WHERE DATEDIFF(DAY, MyDate, '2010-01-01') < 365
PRINT DATEDIFF(MS, @Stopwatch, GETDATE())
Excuse me for posting late and my crudely commented example, but I think it important to mention SARG.
请原谅我发布晚了和我粗暴评论的例子，但我认为提到SARG很重要。
SELECT I.Fee
FROM Item I
WHERE I.DateCreated > DATEADD(DAY, -364, GETDATE())
Although the temp table in the code below has no index, the performance is still enhanced by the fact that a comparison is done between an expression and a value in the table and not an expression that modifies the value in the table and a constant. Hope this is found to be useful.
虽然下面代码中的临时表没有索引，但由于比较是在表达式和表中的值之间进行的，而不是在修改表中的值和常量的表达式之间进行的，因此性能仍然得到增强。希望这被发现是有用的。
USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.#ArbDates') IS NOT NULL DROP TABLE #ArbDates
DECLARE @Stopwatch DATETIME
----------------------------------
-- Build test data: 100000 rows
----------------------------------
;WITH Base10 (n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
)
,Base100000 (n) AS
(
SELECT 1
FROM Base10 T1, Base10 T3, Base10 T4, Base10 T5, Base10 T6
)
SELECT MyDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO #ArbDates
FROM Base100000
----------------------------------
-- Monitor time differences
----------------------------------
SET @Stopwatch = GETDATE()
-- NOT SARGABLE
SELECT *
FROM #ArbDates
WHERE DATEDIFF(DAY, MyDate, '2010-01-01') < 365
PRINT DATEDIFF(MS, @Stopwatch, GETDATE())
SET @Stopwatch = GETDATE()
-- SARGABLE
SELECT *
FROM #ArbDates
WHERE MyDate > DATEADD(DAY, -364, '2010-01-01')
PRINT DATEDIFF(MS, @Stopwatch, GETDATE())
回答by Rick
How about
怎么样
Select I.Fee
From Item I
WHERE (days(GETDATE()) - days(I.DateCreated) < 365)
回答by aykut aydo?an
SELECT DATEDIFF(day,'2014-06-05','2014-08-05') AS DiffDate
diffdate is column name.
diffdate 是列名。
result:
结果：
DiffDate
差异日期
23
23
回答by Art
SELECT (to_date('02-JAN-2013') - to_date('02-JAN-2012')) days_between
FROM dual
/
