CODEWITHSUNDEEP
sqlsql-serversql-server-2008
prabu R
prabu R

Reputation: 2209

Difference between two dates in exact number of hours in SQL

I would like to calculate the exact hours difference between two datetime variables. The hours difference should be exact like this:

1.5
2
6.25

Anybody please help out..Thanks in advance...

Upvotes: 13

Views: 106391

Answers (5)

Philip Wade
Philip Wade

Reputation: 358

Keep it simple:

declare @date1 datetime
declare @date2 datetime

select @date1 = GETDATE();
select @date2 = '2013-02-02 14:05'

select DATEDIFF(hh, @date2, @date1)


Results
-----------
71

(1 row(s) affected)

Upvotes: 5

Samjin Mahadevan
Samjin Mahadevan

Reputation: 67

To get Exact Time Difference in HH:MM try the below code in MS-SQL

 Declare @InTime datetime='2017-11-27 10:00:00',
 @OutTime datetime='2017-11-27 11:15:00'

 SELECT CONVERT(varchar(5),DATEADD(minute,DATEDIFF(minute,@InTime,@OutTime),0), 114)
 -----------
 Result
 01:15

Upvotes: 5

TechDo
TechDo

Reputation: 18629

Please try:

declare @dt1 datetime, @dt2 datetime, @Seconds int
select @dt1='2013-02-05 14:05:55.113', @dt2 =getdate()
set @Seconds=datediff(second, @dt1, @dt2)

    declare @Hour nvarchar(50)
    declare @Min nvarchar(50)
    declare @MinTemp int        
    if @Seconds >0 
    begin
    set @Hour=cast((@Seconds / 3600) as nvarchar(20)) +' Hrs '
    set @MinTemp= (@Seconds % 3600) / 60
    set @Min=cast(@MinTemp as nvarchar(20))
    if  @MinTemp<10 
        select @Hour+'0'+@Min +' Min'
    else
        select @Hour+@Min +' Min'
    end
    else 
    select '00 Hrs 00 Min'

Upvotes: 1

Pandian
Pandian

Reputation: 9126

it will help you....

  Declare @Date1 dateTime
  Declare @Date2 dateTime 
  Set @Date1 = '22:30:00'   
  Set @Date2 = '00:00:00'
  Select Cast((@Date1 - @Date2) as Float) * 24.0

Upvotes: 5

Daniel Kelley
Daniel Kelley

Reputation: 7737

You could use DATEDIFF to find the difference in minutes and convert that into hours:

select datediff(mi, startdate, enddate)

Assuming 1.5 means 1 hour and 30 minutes you could simply divide the result by 60:

select datediff(mi, startdate, enddate) / 60.0

Upvotes: 33

Related Questions