Subtract hours in SQL

Question

I am having hours(06:42,9:30,..) in DB. I want to subtract the hours from 08:00

I tried the below query

Select totalhrsinside as hour,convert(varchar, dateadd(minute, -8, totalhrsinside), 100) as diff from table_name

But it does not work as expected

Output should be

hour    diff
06:42   -01:18 
09:30   01:30

Duplicate of https://stackoverflow.com/questions/12756769/sql-convert-datetime-and-subtract-hours#12756867 — M. Mimpen, Jun 27 '17 at 07:26
@M.Mimpen I tried this link already. It outputs the result with date value. My expectation is only hour. Please see my sample Output — ManiMuthuPandi, Jun 27 '17 at 07:30
How about this one then? https://stackoverflow.com/questions/1114307/extracting-hours-from-a-datetime-sql-server-2005 — M. Mimpen, Jun 27 '17 at 07:38

HoneyBadger · Accepted Answer · 2017-06-27T08:32:04.433

5

I think you are looking for the difference in time, not to subtract or add.

SELECT  Hours
,       CONCAT(
                    CASE WHEN SIGN(diff) = -1 THEN '-' END
                  , FORMAT(ABS(diff/60), '0#')
                  , ':'
                  , FORMAT(ABS(diff%60), '0#')
              ) diff
FROM    (
            SELECT  '6:42' Hours, DATEDIFF(MINUTE, '8:00', '6:42') diff UNION ALL
            SELECT  '9:30' Hours, DATEDIFF(MINUTE, '8:00', '9:30') diff 
        ) D

Result:

+-------+-------+
| Hours | diff  |
+-------+-------+
| 6:42  | -01:18 |
| 9:30  | 01:30  |
+-------+-------+

edited Jun 27 '17 at 08:32

answered Jun 27 '17 at 07:43

HoneyBadger

14,750
3
34
48

1

`SELECT CONCAT (FORMAT(diff / 60, '0#'), ':', FORMAT(ABS(diff % 60), '0#'))` will return with the padding of zero as per the OP's expected result `-01:18, 01:30` – Arulkumar Jun 27 '17 at 08:00
1

There's a small bug in this solution, it will not display a negative sign when the diff is smaller than 1 hour. Maybe use something like this in the select `CONCAT(CASE WHEN SIGN(diff) = -1 THEN '-' END, ABS(diff/60), ':', ABS(diff%60))` – MWillemse Jun 27 '17 at 08:25

LukStorms · Answer 2 · 2017-06-27T09:33:01.383

Using a case to add a minus sign if the time is lower.

select 
totalhrsinside as [hour],  
concat(
  case when DateDiff(minute, '08:00', totalhrsinside) < 0 then '-' end, 
  left(dateadd(minute, abs(datediff(minute, '08:00', totalhrsinside)), cast('00:00' as time)), 5)
) as diff
from 
(
 select totalhrsinside 
 from (values 
 ('06:45'),
 ('08:00'),
 ('09:30')
 ) v(totalhrsinside)
) testvalues;

Returns:

06:45   -01:15
08:00   00:00
09:30   01:30

Subtract hours in SQL

2 Answers2