How to get Date diffrence in hh:mm format In Select Query Sql 2005

Question

Hi I am trying to get the the result of in time and out time from dates but it returns only hours using following select Query as follows

SELECT DATEDIFF(Hh,InTime,OutTime)  as Diff_time from EmpLogTable

and i need result in HH:MM Suppose my in time is 11 am and out is 5.49pm so o/p would be 6.49 but using above select query i am getting o/p as 7 only if any body has a solution then please let me know

Thanking you in Advance Umesh Rakhe

Answer 1

SELECT Outime - InTime as Diff_time from EmpLogTable

DATEDIFF measures day, hour etc boundaries: you're asking for a true date/time difference. In this case, I'd simply subtract the values rather than using a complex nested DATEDIFF. Or do it in the client.

If you have a interval > 24 hours though, then you'd need a nested DATEDIFF to get 25 hours: my answer would give one day and one hour.

The hh:nn:ss format can be done via CONVERT with style 108, but again I'd do this in the client

Answer 2

The DATEDIFF function returns an INT so it will not work as you like, your best bet is to subtract InTime from OutTime or use DATEDIFF with minutes (n) instead.

Answer 3

1. you should probably do UI formatting on the client, not the database

2. you can use diff = datediff(mi, intime, outtime) to get the difference in minutes

then divide diff by 60 to get the hours

and take the modulus diff % 60 to get the remaining minutes

then turn into strings and you're good to go