Join Hours and Minute column for showing in Decimal format SQL Query

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       |
+--------------------------+------------------------+

​x
 
+--------------------------+------------------------+| 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

 
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 |
+--------------------------+------------------------+-------+---------+

 
+--------------------------+------------------------+-------+---------+| 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       |
+--------------------------+------------------------+------------+

 
+--------------------------+------------------------+------------+| 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       |+--------------------------+------------------------+------------+​

Answer

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

 
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​

Advertisement

Answer