Subtract two dates in SQL and get days of the result

Question

Select I Fee From Item I WHERE GETDATE   - I DateCreated  lt  365 days   How can I subtract two days  Result should be days  Ex  365 days  500 days   etc

User · Answer

SELECT  to date  02-JAN-2013   - to date  02-JAN-2012    days between FROM dual

User · Answer

Use DATEDIFF  Select I Fee From Item I WHERE  DATEDIFF day  GETDATE    I DateCreated   lt  365

User · Answer

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  gt  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    lt  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  gt  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    lt  365  PRINT DATEDIFF MS   Stopwatch  GETDATE    SET  Stopwatch   GETDATE        -- SARGABLE     SELECT       FROM  ArbDates     WHERE MyDate  gt  DATEADD DAY  -364   2010-01-01    PRINT DATEDIFF MS   Stopwatch  GETDATE

User · Answer

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

User · Answer

use DATE DIFF  Select I Fee From   Item I WHERE  DATEDIFF day  GETDATE    I DateCreated    lt  365    DATE DIFF

User · Answer

How about   Select I Fee From Item I WHERE   days GETDATE    - days I DateCreated   lt  365

User · Answer

SELECT DATEDIFF day  2014-06-05   2014-08-05   AS DiffDate   diffdate is column name   result   DiffDate  23

[sql] Subtract two dates in SQL and get days of the result

Examples related to sql

Examples related to sql-server

Examples related to tsql