CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008time
angel
angel

Reputation: 4632

How to get a time between two columns of type time?

I have a table in SQL Server

id int, 
enters time, 
left time, 
date datetime,
user varchar(50)

Now I want to get the time difference between enters and lefts but I get this error message:

Msg 8117, Level 16, State 1, Line 1
Operand data type time is invalid for subtract operator.

How can I to get the time has passed?
For example, if user enters at 8.00 and leaves at 9.15 I need to get 1.15.
How can I do this?

Upvotes: 3

Views: 5199

Answers (4)

jack.mike.info
jack.mike.info

Reputation: 128

Simple method is

convert the DATE to time datatype

convert()

and then use substring to find the difference based on the position

between substr(date,0,0) and time

Upvotes: 0

user691102
user691102

Reputation: 

select
     id
    ,enters
    ,[left]
    ,date
    ,[user]
    ,CONVERT(time,DATEADD(second, DATEDIFF(second,enters,[left]), 0)) as [Difference]
from
    MyTable

Upvotes: 1

Alex Aza
Alex Aza

Reputation: 78447

declare @MyTable table(enters time, lefttime time)
insert @MyTable values('8:00', '9:05')

select *,
    cast(datediff(hour, enters, lefttime) as varchar) + ':' + 
        right('0' + cast(datediff(minute, enters, lefttime) % 60 as varchar), 2)
from @MyTable

[EDIT]

Added 0 padding to avoid showing 1:5, instead of 1:05.

Another way to do this:

select cast(dateadd(minute, datediff(minute, enters, lefttime), 0) as time)
from @MyTable

The result will be of time type, not varchar.

Upvotes: 4

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56162

Look at DATEDIFF function.

DECLARE @enters datetime = CAST('2011-06-26T08:00:00' as datetime);
DECLARE @left datetime = CAST('2011-06-26T09:15:00' as datetime);

SELECT CAST(DATEDIFF(HH, @enters, @left) as varchar(max)) 
    + ':' + CAST(DATEDIFF(MI, @enters, @left) % 60 as varchar(max))

Upvotes: 4

Related Questions