[sql] 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...

This question is related to sql sql-server tsql

The answer is


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

use DATE_DIFF

Select I.Fee
From   Item I
WHERE  DATEDIFF(day, GETDATE(), 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


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)

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL