[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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

0
kdeutsch
Asked:
kdeutsch
1 Solution
 
Om PrakashCommented:
Try:

Select      
      strUIC,            
        strFullName,
      CAST(DATEDIFF(hour, dtTimeIN, dtTimeOut) 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
0
 
Alpesh PatelAssistant ConsultantCommented:
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'
 
0
 
tigin44Commented:


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
0
 
kdeutschAuthor Commented:
Thanks, works great
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now