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