Solved

# Find difference in hours and minutes

Posted on 2011-04-25
226 Views
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
``````
0
Question by:kdeutsch

LVL 22

Accepted Solution

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
0

LVL 21

Expert Comment

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

LVL 26

Expert Comment

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
0

Author Closing Comment

Thanks, works great
0

## Featured Post

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