DATEDIFF between two dates to return timestamp

Question

SOLVED

CONCAT((DATEDIFF(Minute,START_DTTM,END_DTTM)/60),'h:',
       (DATEDIFF(Minute,START_DTTM,END_DTTM)%60) 'm') AS TotalTimeMissing 

Produces TotalTimeMissing: 5h:13m

________

I'm trying to return a timestamp value between two specific dates to work out the time between a package going missing and it being found.

EDIT: Code has been updated to include code from Sami. I have also added additional code that I excluded from the original.

Here is the current code:

USE PACKAGE

GO  

SELECT

        dp.LEGACY_ID

       ,dp.SURNAME

       ,dp.FORENAME

       ,dp.ETHNICITY_DESCRIPTION

       ,dp.BIRTH_DTTM

       ,DATEDIFF(YY, dp.BIRTH_DTTM, GETDATE()) -

            CASE

                  WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >=

                        RIGHT(CONVERT(VARCHAR(6), dp.BIRTH_DTTM, 12), 4)

                        THEN 0

                  ELSE 1

            END AS [Current Age]

--^Precise age calc due to potential LL inaccuracy

       ,mp.DIM_PERSON_ID

       ,mp.MISSING_STATUS

       ,mp.START_DTTM

       ,mp.END_DTTM

       ,dp.LEGACY_ID

       ,mp.RETURN_INT_OFFERED

       ,mp.RETURN_INT_ACCEPTED

       ,mp.RETURN_INT_DATE


FROM C_S.FACT_MISSING_PACKAGE AS mp

JOIN C_S.FACT_MISSING_PACKAGE AS dp ON mp.DIM_PERSON_ID = dp.DIM_PERSON_ID

WHERE CAST (mp.START_DTTM AS DATE)

              BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE)

--^Displays all records within exactly 1 year of run date

       UNION (SELECT CONCAT(Value / 3600 / 24,

              ' Days ',

              RIGHT(CONCAT('00', Value / 3600 % 24), 2),

              ':',

              RIGHT(CONCAT('00', Value / 60 % 60), 2),

              ':',

              RIGHT(CONCAT('00',Value % 3600 % 60), 2)

       ) AS TotalTimeMissing

FROM

(

  SELECT mp.DIM_PERSON_ID, DATEDIFF(Second, mp.START_DTTM, mp.END_DTTM) Value

  FROM C_S.FACT_MISSING_PACKAGE AS mp

) T(Value))


ORDER BY START_DTTM ASC;

Sami got me most of the way there but when I run the above code I get an error in relation to the UNION and T, with T not stating the required number of columns. To solve this I tried putting the initial round of SELECT columns into the (SELECT CONCAT() statement but it generates errors so I'm at a bit of loss when it comes to how fix it?

I need to return all of these columns with an additional column at the end as TotalTimeMissing

Thanks

Answer 1

You can calculate the difference in minutes first using the DATEDIFF function and then calculate hours and days knowing that 1 hour is 60 minutes and 1 day is 1440 minutes.

Be aware of how the DATEDIFF works in SQL Server:

This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

So,

DATEDIFF(day, '2020-01-13 23:59:58', '2020-01-14 00:00:08')

will return 1, even though the difference is only few seconds, because the given interval crosses the boundary of a day (midnight).

That's why you should not be using DATEDIFF(day, ...) here, but use DATEDIFF(minute, ...) or DATEDIFF(second, ...) and calculate the number of hours and days based on the total number of elapsed minutes or seconds.

I will use the CROSS APPLY to avoid typing the long expression many times. I also use integer division / here which discards the fractional part, e.g. 200 / 60 = 3.

Total days = total minutes / 1440 (discard fractional part)
Total hours = total minutes / 60 (discard fractional part)

But, we don't need total hours, we need hours that are left extra after the total days, so we need to take mod 24.

Hours = Total hours % 24

For final minutes we need just the remainder of the minutes after total days and hours, hence

Minutes = total minutes mod 60.

The query:

SELECT 
     dp.LEGACY_ID
    ,dp.SURNAME
    ,dp.FORENAME
    ,mp.DIM_PERSON_ID
    ,mp.MISSING_STATUS
    ,mp.START_DTTM
    ,mp.END_DTTM
    ,dp.LEGACY_ID
    ,STR(DiffMinutes / 1440) + ':' +        -- total days
     STR(DiffMinutes / 60 % 24) + ':' +     -- hours (0 .. 23)
     STR(DiffMinutes % 60) AS TimeMissing   -- minutes (0 .. 59)
FROM 
    MissingPackages AS mp
    JOIN DIM_PERSON AS dp ON mp.DIM_PERSON_ID = dp.DIM_PERSON_ID
    CROSS APPLY
    (
        SELECT DATEDIFF(minute, mp.START_DTTM, mp.END_DTTM) AS DiffMinutes
    ) AS A
ORDER BY START_DTTM ASC;

Answer 2

Is this what're looking for

CREATE TABLE MyData 
(
  StartDate DATETIME, 
  EndDate DATETIME
);

INSERT INTO MyData VALUES
('2017-01-01 00:00:00', '2018-01-02 00:25:01'),
('2017-01-01 00:00:00', '2018-01-01 00:00:00'),
('2017-01-02 12:00:09', '2017-01-02 12:00:30'),
('2017-01-01 02:00:00', '2017-01-01 03:30:30'),
('2017-01-01 00:00:00', '2017-01-03 00:30:30'),
('2017-12-31 23:59:59', '2018-01-01 00:00:01'),
('2017-12-31 23:59:01', '2018-01-01 00:00:01');


SELECT CONCAT(Value / 3600 / 24, 
              ' Days ', 
              RIGHT(CONCAT('00', Value / 3600 % 24), 2), 
              ':', 
              RIGHT(CONCAT('00', Value / 60 % 60), 2), 
              ':',
              RIGHT(CONCAT('00',Value % 3600 % 60), 2)
       ) TimeMissing 
FROM
(
  SELECT DATEDIFF(Second, StartDate, EndDate) Value
  FROM MyData
) T(Value);

Returns:

+-------------------+
|    TimeMissing    |
+-------------------+
| 366 Days 00:25:01 |
| 365 Days 00:00:00 |
| 0 Days 00:00:21   |
| 0 Days 01:30:30   |
| 2 Days 00:30:30   |
| 0 Days 00:00:02   |
| 0 Days 00:01:00   |
+-------------------+

Answer 3

CONCAT(DATEDIFF(day, START_DT, END_DT), '-', DATEDIFF(hour, START_DT, END_DT), '-', DATEDIFF(minute, START_DT, END_DT)) AS TimeMissing