I’m trying to calculate Hours and Minutes, but the minutes portion is dropping the leading 0 and it appears the calculation is incorrect.
Example, 9.06 is displaying as 9.6 using this query against the values
ClockIn 2020-01-07 23:00:53.000
ClockOut 2020-01-08 07:06:52.000
This results in a calculated time of 8.6:
concat(datediff(n,ClockIn,ClockOut)/60,'.', case when len(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)) = 1 then concat(0,cast(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)as varchar(2))) else datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60) end )
Advertisement
Answer
Final version that works is as follows:
concat(cast(datediff(n,ClockIn,ClockOut)/60 as varchar(2)),'.', case when len(cast(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)as varchar(2))) = 1 then concat(0,cast(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)as varchar(2))) else cast(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60) as varchar(2)) end )