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 |
-------------------------- ------------------------ ------------
CodePudding user response：
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;
CodePudding user response：
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