Find difference in hours and minutes

Posted on 2011-04-25
Last Modified: 2012-05-11
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,		
	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

Question by:kdeutsch
    LVL 22

    Accepted Solution


          CAST(DATEDIFF(hour, dtTimeIN, dtTimeOut) as varchar(10)) + ':' + CAST(DATEDIFF(MINUTE, dtTimeIN, dtTimeOut) % 60 as varchar(10))
          tblMNNatPersonnel as mn
          INNER JOIN tblSRPAttendance as a on a.strSSN = mn.strSSN
    LVL 21

    Expert Comment

    by: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'
    LVL 26

    Expert Comment


    Select      strUIC,            
          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

    Author Closing Comment

    Thanks, works great

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now