asked on

Find difference in hours and minutes

Trying to find the difference in hours and minutes of 2 times. in the Db they are datetime fields

dtTimeIn dtTimeOUt
2011-04-21 13:04:30.000      2011-04-21 13:18:43.067
2011-04-21 13:04:42.000      2011-04-21 15:18:43.067
2011-04-21 13:06:09.000      2011-04-21 16:25:43.067
2011-04-21 13:12:57.000      2011-04-21 16:25:43.067

SO here is what I attempted but I get a value of 6 becuas eit adds them together and when i put in a ':' then it gives me an error of 
Conversion failed when converting the varchar value '0:' to data type int.

Select	strUIC,		
         strFullName,
	CAST(DATEDIFF(hour, dtTimeIN, dtTimeOut) as varchar(10)) + ':' + CAST(DATEDIFF(MINUTE, dtTimeIN, dtTimeOut) as varchar(10)) / 60
from	tblMNNatPersonnel as mn INNER JOIN
		tblSRPAttendance as a on a.strSSN = mn.strSSN

Open in new window

ASKER CERTIFIED SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alpesh Patel

SElect convert(varchar,((DAteDiff(minute,'2011-04-21 13:04:30.000', '2011-04-21 16:25:43.067'))/60)) + ' Hours ' + convert(varchar,((DAteDiff(minute,'2011-04-21 13:04:30.000', '2011-04-21 16:25:43.067'))%60)) + ' Minutes'

tigin44

Select      strUIC,
strFullName,
      CAST(DATEDIFF(MINUTE, dtTimeIN, dtTimeOut)/ 60 as varchar(10)) +':'+ CAST(DATEDIFF(MINUTE, dtTimeIN, dtTimeOut) % 60 as varchar(10))
from      tblMNNatPersonnel as mn INNER JOIN
            tblSRPAttendance as a on a.strSSN = mn.strSSN

kdeutsch

ASKER

Thanks, works great