Subtract two dates in SQL and get days of the result

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

7 Answers

Use DATEDIFF

Select I.Fee From Item I WHERE DATEDIFF(day, GETDATE(), I.DateCreated) < 365 

use DATE_DIFF

Select I.Fee From Item I WHERE DATEDIFF(day, GETDATE(), I.DateCreated) < 365 

• DATE_DIFF

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.

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()) 

SELECT DATEDIFF(day,'2014-06-05','2014-08-05') AS DiffDate 

diffdate is column name.

result:

DiffDate

23

How about

Select I.Fee From Item I WHERE (days(GETDATE()) - days(I.DateCreated) < 365) 

SELECT (to_date('02-JAN-2013') - to_date('02-JAN-2012')) days_between FROM dual / 

Syntax

DATEDIFF(expr1,expr2)

Description

DATEDIFF() returns (expr1 – expr2) expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

@D Stanley