CODEWITHSUNDEEP
sqlsql-serverdatetime
user2615341
user2615341

Reputation: 89

difference between dates in hours

I have searched many of the threads and cant find the answer I need. I have two datetime columns:

BOOKDATIME                BOOKDATIME_OFF 
2013-06-01 12:14:00.000   2013-06-03 07:09:00.000

What I want to do is to just show the total hour and minute difference between the two dates.

When I use:

CONVERT(TIME, BOOKDATIME_OFF - BOOKDATIME) 
                  AS HourMinuteSecond

I get the error message: Cannot call methods on time

Upvotes: 1

Views: 8979

Answers (2)

Hart CO
Hart CO

Reputation: 34784

You can use DATEDIFF() and division/modulus division to get difference in total Hours/Minutes:

SELECT Hours = DATEDIFF(Minute,'2013-06-01 12:14:00.000','2013-06-03 07:09:00.000')/60
      ,Minutes = DATEDIFF(Minute,'2013-06-01 12:14:00.000','2013-06-03 07:09:00.000')%60

If the DATETIME fields are always less than a day apart then you can also subtract the datetime's and cast as time:

DECLARE @date DATETIME = GETDATE()
       ,@date2 DATETIME = DATEADD(MINUTE,25,GETDATE())
SELECT CAST(@date2 - @date AS TIME)

Upvotes: 1

Yosi Dahari
Yosi Dahari

Reputation: 7009

DATEDIFF(HH,BOOKDATIME, BOOKDATIME_OFF)

Upvotes: 1

Related Questions