How can I find the time difference in two dates?

Question

I am trying to use the DATEDIFF() function to find the difference between two dates within a table. The problem I am having is the understanding how to subtract the time from the most recent date in the table VS the starting date.

Dates are in the format: YYYY-MM-DD HH:MM:SS

I have tried this:

select FileName, '20:00' as StartTime, ModifiedDate, DATEDIFF(MINUTE, 
'20:00', ModifiedDate) as 'BackupTime' 
from BackLogData

But it returns the minutes from the start time.

Here is a sample of the table:

+-----------+-----------------------------+------------+
| StartTime |        ModifiedDate         | BackupTime |
+-----------+-----------------------------+------------+
| 20:00     | 2019-06-10 01:04:17.3692999 |   62817424 |
| 20:00     | 2019-06-10 00:53:23.4900986 |   62817413 |
| 20:00     | 2019-06-10 00:51:09.2363761 |   62817411 |
+-----------+-----------------------------+------------+

The correct table:

+-----------+-----------------------------+------------+--+
| StartTime |        ModifiedDate         | BackupTime |  |
+-----------+-----------------------------+------------+--+
| 20:00     | 2019-06-10 01:04:17.3692999 |         11 |  |
| 20:00     | 2019-06-10 00:53:23.4900986 |          2 |  |
| 20:00     | 2019-06-10 00:51:09.2363761 |        291 |  |
+-----------+-----------------------------+------------+--+

Answer 1

To get the previous time you can join your table with itself. But first I would number the rows on a CTE, so you can now set an easy condition to join each row with the previous row.

This query returns the difference between each ModifiedTime and its previous one (or StartDate on the first row), resulting in the desired result set that you have posted :

declare @StartTime time = convert(time, '20:00');

declare @StartDate datetime = (select convert(datetime, dateadd(day, -1, convert(date, max(ModifiedDate)))) + 
                                      convert(datetime, @StartTime)
                               from BackLogData);

with LOGS as (
  select ModifiedDate,
         row_number() over (order by ModifiedDate) as num
  from BackLogData
)
select @StartTime as StartTime, 
       LOGS.ModifiedDate, 
       datediff(minute, 
                case when LOGS.num = 1 then @StartDate else PREVIOUS.ModifiedDate end,
                LOGS.ModifiedDate) as BackupTime
from LOGS
     left join LOGS as PREVIOUS on PREVIOUS.num = LOGS.num - 1
order by LOGS.num

PS: As Caius Jard noted, to be able to directly calculate the time difference between ModifiedDate and StartTime, we have to convert StartTime to a datetime using the date part of the last ModifiedDate minus one (meaning it started the day before).

Answer 2

Marc Guillot was on the right track but i found some issues with his query. Here's a revision:

--this is setup, you don't need this
CREATE TABLE t
    ([StartTime] time, [ModifiedDate] datetime)
;

INSERT INTO t
    ([StartTime], [ModifiedDate])
VALUES
    ('20:00', '2019-06-10 01:04:17'),
    ('20:00', '2019-06-10 00:53:23'),
    ('20:00', '2019-06-10 00:51:09')
;

--we now have a table with a TIME column (cast it in the cte if yours is not), a DATETIME
with LOGS as (
  select StartTime,
         ModifiedDate,
         DATEADD(DAY, -1, CAST(CAST(ModifiedDate as DATE) as DATETIME)) as ModifiedMidnightDayBefore,
         CAST(StartTime as DateTime) as StartDateTime,
         row_number() over (order by ModifiedDate) as num
  from t
)
select curr.StartTime, 
       curr.ModifiedDate, 
       datediff(minute, 
            COALESCE(
               prev.ModifiedDate, 
               curr.ModifiedMidnightDayBefore + curr.StartDateTime
            ),
            curr.ModifiedDate) as BackupTime
from 
  LOGS curr
  left join LOGS as prev on prev.num = curr.num - 1
order by curr.num

The LOGS CTE is joined to itself on num = num-1 thereby putting the current row and previous row data together on a row. One row will have no previous data (blank) so when we are doing our datediff, we use coalesce, which is like ISNULL but is supported by all major db vendors. COALESCE returns the first non null argument. It is used to fill in a value if there is no PREVious value for the modified date

DATEDIFF of prev vs current is fairly obvious. The trick is in the logic if ther eis no previous value:

The CTE also casts the modifieddate datetime, to a date, to drop the time component (set it to midnight) and back to a datetime (so it emerges from the dateadd as a datetime). Dateadd subtracts one day from it, so it is midnight on the pervious day, and then we add our start time (8pm) to this. So effectively the min date in the table is converted to midnight, bumped back a day and then has 8pm added, so it becomes "8pm on the day prior to the modified date", and then we can datediff this nicely to 291 minutes

Answer 3

If all you're wanting is the difference of minutes from hour '20:00' compared to the time of ModifiedDate, you have to just compare the time values:

Try:

SELECT [FileName]
     , '20:00' AS [StartTime]
     , [ModifiedDate]
     , DATEDIFF(MINUTE, '20:00', CONVERT(TIME, [ModifiedDate])) AS 'BackupTime' --convert your modified date to time 
FROM   [BackLogData];

Reason your getting a weird large value is you were trying to basically find the different between 1900-01-01 20:00 and your ModifiedDate.

Answer 4

You can take your difference in minutes and transform it to time datatype using dateadd and cast. Please note that if your difference is bigger then 24 hours then this won't work (time data type stores up to 24 hours).

SELECT FileName, '20:00' AS StartTime, ModifiedDate,
cast(dateadd(minute,DATEDIFF(MINUTE, RecordDate, ModifiedDate),'19000101') as time(0)) AS 'BackupTime' 
FROM BackLogData

Example:

SELECT 
cast(dateadd(minute,DATEDIFF(MINUTE, '2019-05-05 16:00:00', '2019-05-05 18:00:00'),'19000101') as time(0)) AS 'BackupTime' 

Output:

02:00:00