• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

How to find number of hours between two datetime fields in SQL server

I have two datetimestamp fields  dt1 and dt2
I need to find the difference in hours between dt1 and dt2 in SQL Server

e.g
dt1 = 2010-11-14 13:00:00.000
dt2 = 2010-11-14  15:00:00.000
result is 2 hours

dt1 = 2010-11-13 13:00:00.000
dt2 = 2010-11-14  15:00:00.000
result is 26 hours

dt1 = 2010-11-14 13:00:00.000
dt2 = 2010-11-14  15:30:00.000
result is 2.5 hours
0
countrymeister
Asked:
countrymeister
  • 2
2 Solutions
 
QlemoC++ DeveloperCommented:
Depending on the resolution you need, you can do  simple calculation:

select (dt2-dt1)*24
or
select datediff(mi, dt1, dt2)/60.0
or the like.
0
 
James MurrellProduct SpecialistCommented:
You want the DATEDIFF function:

SELECT DATEDIFF(hh, @date1, @date2)

if you want minutes as well
select DATEDIFF(hh, @date1, @date2) as Hours_Difference,  
    DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @date1, @date2),@date1),@date2) as Minutes_Difference

0
 
QlemoC++ DeveloperCommented:
Sorry, the first one is not correct, we need to convert it to float:

select convert(float, dt2-dt1) * 24

dt2-dt1 results in a datetime representing the difference between both dates. Converting to float it is expressed as fractions of days, so multiplying with 24 (hours per day) makes it fractions of hours.
0
 
cyberkiwiCommented:
the first form from qlemo doesn't work in sql server
2nd one does, but depending on precision, you could also use

datediff(ss, dt1, dt2) / 3600.0

or
round((convert(float,d2)-convert(float,d1))*24,5)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now