CODEWITHSUNDEEP
sqldatecompare
Christian
Christian

Reputation: 29

Calculate difference of two dates in SQL

I have a table that contains 2 date/time columns (CreatedDateTime / LastModifiedDateTime):

Item   CreatedDateTime            LastModifiedDateTime

1      2016-03-18 18:49:36.557    2016-03-22 18:51:10.950

2      2016-03-22 17:32:57.610    2016-03-22 17:37:26.790

I would like to:

  1. Calculate the difference between CreatedDateTime and today's date (to give me an 'age')
  2. Calculate the difference between LastModifiedDateTime and today's date (to give me an idea of iactivity)

With 2. above, I want to be able to report on an Item that has not been 'modified' (LastModifiedDateTime) in a while.

Can anyone point me in the right direction?

Upvotes: 0

Views: 22205

Answers (3)

Arpit Porwal
Arpit Porwal

Reputation: 293

The DATEDIFF() function returns the time between two dates.

Syntax DATEDIFF(datepart,startdate,enddate);

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

Result

DiffDate

61

Swapping the dates would result in a negative result.

Upvotes: 2

Fuzzy
Fuzzy

Reputation: 3810

If you are using SQL SERVER:

SAMPLE DATA:

CREATE TABLE #Temp(Item                 INT
              , CreatedDateTime      DATETIME
              , LastModifiedDateTime DATETIME);

INSERT INTO #Temp
VALUES
      (1
     , '2016-03-18 18:49:36.557'
     , '2016-03-22 18:51:10.950'),
      (2
     , '2016-03-22 17:32:57.610'
     , '2016-03-22 17:37:26.790');

QUERY:

SELECT A.Item
    , A.CreatedDateTime
    , A.LastModifiedDateTime
    , Age = DATEDIFF(day,A.CreatedDateTime,GETDATE())
    , inactivity = DATEDIFF(day,A.LastModifiedDateTime,GETDATE())
FROM   #Temp AS A;

RESULT:

enter image description here

Upvotes: 4

IceCreamSandwich
IceCreamSandwich

Reputation: 43

You can use the DATEDIFF() function for the age and GETDATE() function for the current date.

You will just need to enter DATEDIFF(day,CreatedDateTime,GETDATE()), or a similar query.

Datediff() will also allow you to take month, hour, or other time measures.

Also with #2, you could throw in some CASE WHEN LastModifiedDateTime IS NULL THEN ... to give yourself a different report on that.

Upvotes: 0

Related Questions