CODEWITHSUNDEEP
sqlsql-server
PBeezy
PBeezy

Reputation: 1272

Find difference between datetime and time in SQL

I have a datetime variable

2017-03-10 13:05:00.000

and a time variable

12:00:00.0000000

I need to find the difference between the datetime and the time on the datetime's date so I get 1 hour and 5 minutes.

Upvotes: 0

Views: 50

Answers (3)

puzeledbysql
puzeledbysql

Reputation: 194

select datediff( minute, cast('2017-03-10 13:05:00.000' as time) , cast ('12:00:00.0000000' as time))

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82020

Declare @D datetime = '2017-03-10 13:05:00.000'
Declare @T time     = '12:00:00.0000000'

Select AsMinutes = DateDiff(MINUTE,@T,cast(@D as time))
      ,AsTime    = Format(DateAdd(MINUTE,DateDiff(MINUTE,@T,cast(@D as time)),0),'HH:mm')
      ,AsString  = replace(Format(DateAdd(MINUTE,DateDiff(MINUTE,@T,cast(@D as time)),0),'H x1 m')+' minutes','x1','hour')

Returns

AsMinutes   AsTime  AsString
65          01:05   1 hour 5 minutes

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Hmmm . . .

select dateadd(millisecond,
               datediff(millisecond, cast(datetimevar as time), timevar)
               0)

You probably want to cast the result back to a time data type.

Upvotes: 0

Related Questions