SQL Date Diff between Upload time 1 and 2

Question

I'm new to SQL and was wondering if you could help me create a query that compares the time difference in Days, Hours, Minutes.

For example,

Start time 1 = 13/01/2016, 16:00
End time 1 = 14/01/2016, 18:00

So the time difference should be 1 day and 2 hours right?

Another example.

Start time 2 = 20/10/2016, 12:00
End time 2 = 20/10/2016, 16:00

So the

time difference is just 2 hours.

The reason I need this query is to create a report based on the difference in time between a start time and a finish time of a task.

Possible duplicate of [DATEDIFF in HH:MM:SS format](http://stackoverflow.com/questions/14445600/datediff-in-hhmmss-format) — sr28, Nov 11 '16 at 11:54

Cato · Answer 1 · 2016-11-11T12:08:31.287

0

watch out for problems with DATEDIFF that ignores fractions of days etc

DECLARE @Starttime DATETIME = '20160113 16:00';
DECLARE @Endtime   DATETIME ='20160114 15:59';

--calculate difference in seconds then use integer divides and MODULUS to extract days, hours, minutes

;WITH DIFF_SECS AS (SELECT CAST(DATEDIFF(second,@Starttime,@Endtime) AS BIGINT) AS SD)
SELECT SD / (24 * 3600) as NoDays, 
         (SD % (24 * 3600)) / 3600 as NoHours, 
         (SD %  3600)  / 60 AS NoMinutes 
                      FROM DIFF_SECS;

if you aren't using microsoft SQL - then the formulae are possible still useful

if X = 'difference between times in seconds'

then whole days = X / (24 * 3600) where the divide is an integer divide (always rounds down)

edited Nov 11 '16 at 12:08

answered Nov 11 '16 at 11:48

Cato

3,652
9
12

Thanks i found this very useful and pretty much exactly what i asked for, now I just need to make it dynamic – L.gam Nov 11 '16 at 12:49
And yes I am using microsoft SQL server – L.gam Nov 11 '16 at 12:49
@L.gam - glad it helped - you could make the WITH statement your main query, then in the SELECT perform the arithmetic on the seconds value - the WITH statement is to avoid having to calculate seconds differences repeatedly in a single SQL statement – Cato Nov 11 '16 at 12:56

score 0 · Answer 2 · answered Nov 11 '16 at 12:22

In Oracle I would do it like that:

-- this is only to create some data
WITH MyRows AS (
  SELECT to_date('2000/11/30 12:59', 'YYYY/MM/DD HH24:MI') BEGINNING, to_date('2000/11/30 14:01', 'YYYY/MM/DD HH24:MI') ENDING FROM DUAL UNION
  SELECT to_date('2000/12/01 12:59', 'YYYY/MM/DD HH24:MI') , to_date('2000/12/02 00:01', 'YYYY/MM/DD HH24:MI') FROM DUAL UNION
  SELECT to_date('2000/12/30 12:59', 'YYYY/MM/DD HH24:MI') , to_date('2000/12/31 14:01', 'YYYY/MM/DD HH24:MI') FROM DUAL UNION
  SELECT to_date('2000/12/31 12:59', 'YYYY/MM/DD HH24:MI') , to_date('2001/01/01 04:01', 'YYYY/MM/DD HH24:MI') FROM DUAL 
)
-- this is the select
SELECT 
  BEGINNING, 
  ENDING, 
  ENDING-BEGINNING IN_DAYS, 
  (ENDING-BEGINNING)*24 IN_HOURS, 
  (ENDING-BEGINNING)*24*3600 IN_SECONDS, 
  case 
    when (ENDING-BEGINNING)*24*60 >= 3 then round(((ENDING-BEGINNING)*24*60-3)*2)
    else 0
  end Total_cost_in_hamburgers

FROM MyRows
;

timedifferences are days and if you want to calculate with it (f.e. after 3 minutes every minute costs 2 hamburgers) it is better to calculate that way

SQL Date Diff between Upload time 1 and 2

2 Answers2