2

I have two datetime columns in a SQL Server table:

+--------------------------+------------------------+
| CompLast_SignON_datetime | comp_accidentdate_time |
+--------------------------+------------------------+
| 16-12-2021 04:08         | 16-12-2021 05:10       |
| 17-12-2021 14:20         | 17-12-2021 20:00       |
| 18-12-2021 12:15         | 18-12-2021 15:25       |
| 22-12-2021 06:00         | 22-12-2021 12:34       |
| 25-12-2021 11:30         | 25-12-2021 21:40       |
| 26-12-2021 21:00         | 27-12-2021 02:50       |
+--------------------------+------------------------+

From which I have separated hours and minutes through SQL query

SELECT 
    CompLast_SignON_datetime, comp_accidentdate_time, 
    CONVERT(INT, ROUND((DATEDIFF(minute, CompLast_SignON_datetime, comp_accidentdate_time) / 60), 2, 1)) AS 'Hours', 
    FORMAT(CONVERT(INT, DATEDIFF(minute, CompLast_SignON_datetime, comp_accidentdate_time) - CONVERT(INT, (ROUND((DATEDIFF(minute, CompLast_SignON_datetime, comp_accidentdate_time) / 60), 2, 1)) * 60)), '00') AS 'Minutes' 
FROM Safety_SIMS

The output is

+--------------------------+------------------------+-------+---------+
| CompLast_SignON_datetime | comp_accidentdate_time | Hours | Minutes |
+--------------------------+------------------------+-------+---------+
| 16-12-2021 04:08         | 16-12-2021 05:10       |     1 |      02 |
| 17-12-2021 14:20         | 17-12-2021 20:00       |     5 |      40 |
| 18-12-2021 12:15         | 18-12-2021 15:25       |     3 |      10 |
| 22-12-2021 06:00         | 22-12-2021 12:34       |     6 |      34 |
| 25-12-2021 11:30         | 25-12-2021 21:40       |    10 |      10 |
| 26-12-2021 21:00         | 27-12-2021 02:50       |     5 |      50 |
+--------------------------+------------------------+-------+---------+

Now, for further grouping the data, I need to join Hours and Minutes columns in one column for getting output like this

+--------------------------+------------------------+------------+
| CompLast_SignON_datetime | comp_accidentdate_time | Duty_hours |
+--------------------------+------------------------+------------+
| 16-12-2021 04:08         | 16-12-2021 05:10       | 1.02       |
| 17-12-2021 14:20         | 17-12-2021 20:00       | 5.40       |
| 18-12-2021 12:15         | 18-12-2021 15:25       | 3.10       |
| 22-12-2021 06:00         | 22-12-2021 12:34       | 6.34       |
| 25-12-2021 11:30         | 25-12-2021 21:40       | 10.10      |
| 26-12-2021 21:00         | 27-12-2021 02:50       | 5.50       |
+--------------------------+------------------------+------------+
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1185088
  • 99
  • 1
  • 1
  • 8
  • 1
    *Please* use whitespace and linebreaks when writing code. One long line is never easy to read. – Thom A Dec 31 '21 at 12:03
  • 2
    Are you sure you want to just concatenate hours and minutes to appear as a decimal? 5 hours 40 minutes is 5.67 hours – Stu Dec 31 '21 at 12:10
  • If you have an integer and divide by another integer, you get an integer. There is no need to use `ROUND` – Charlieface Dec 31 '21 at 12:28

2 Answers2

2

Since you're showing the minutes instead of a fraction of the hours, it'll be more natural to use : instead of .

Then you can use DATEDIFF and FORMAT to get difference in hours & minutes as a string.

select *
, [Duty_hours] = format(datediff(hour, CompLast_SignON_datetime, comp_accidentdate_time), '00')
                 + format(comp_accidentdate_time - CompLast_SignON_datetime, ':mm')
from Safety_SIMS;
CompLast_SignON_datetime comp_accidentdate_time Duty_hours
2021-12-16 04:08 2021-12-16 05:10 01:02
2021-12-17 14:20 2021-12-17 20:00 06:40
2021-12-18 12:15 2021-12-18 15:25 03:10
2021-12-22 06:00 2021-12-22 12:34 06:34
2021-12-25 11:30 2021-12-25 21:40 10:10
2021-12-26 21:00 2021-12-27 02:50 05:50

Demo on db<>fiddle here

It can be simplified

select *
, [Duty_hours] = format(comp_accidentdate_time - CompLast_SignON_datetime, 'HH:mm')
from Safety_SIMS;

But then you're assuming the difference is always less than a day.

And here's a version that calculates the time difference as a decimal.

select *
, [Duty_hours] = datediff(hour, CompLast_SignON_datetime, comp_accidentdate_time)
                 + cast(format(comp_accidentdate_time - CompLast_SignON_datetime, '.mm') as decimal(10,2))
from Safety_SIMS;
LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • In decimal form, it is easy to group data to get no. of Cases between 00-02, 02-04, 04-06 hours, which is difficult to achieve in hh:mm format. – user1185088 Dec 31 '21 at 13:39
  • Is it really? `where Duty_hours between '01:00' and '06:00'` works fine. Because of the leading 0, the character sort is fine. And btw, you can just replace the `:` by a dot in this solution. – LukStorms Dec 31 '21 at 13:47
  • You are right, just check by replacing ':' with a dot. It is the simplest solution to my issue. you are rock, thanks. – user1185088 Dec 31 '21 at 13:58
  • However, in case the difference is more than a day, it will not work. – user1185088 Dec 31 '21 at 14:05
1

You can try converting both hours and mins to varchar and concate both to make new column.

SELECT 
    CompLast_SignON_datetime, comp_accidentdate_time, 
    CONVERT(VARCHAR(5), ROUND((DATEDIFF(minute, CompLast_SignON_datetime, comp_accidentdate_time) / 60), 2, 1)) + '.' + 
       CONVERT(VARCHAR(5), FORMAT(CONVERT(INT, DATEDIFF(minute, CompLast_SignON_datetime, comp_accidentdate_time) - CONVERT(INT,(ROUND((DATEDIFF(minute, CompLast_SignON_datetime, comp_accidentdate_time) / 60), 2, 1)) * 60)), '00')) AS 'Duty_hours' 
FROM Safety_SIMS
sonam81
  • 157
  • 2
  • 9