CODEWITHSUNDEEP
sqlsql-serverdatetime
Rhushikesh
Rhushikesh

Reputation: 3700

How to get the time difference in two date in SQL Server

I am trying to get the time difference between two dates by query with this code:

DECLARE @firstDate DATETIME
DECLARE @SecondDate DATETIME

SELECT 
    CONVERT(VARCHAR(5), DATEDIFF(dd, @firstDate, @SecondDate)) + ' Days ' + 
    CONVERT(VARCHAR(5), (DATEDIFF(hh, @firstDate, @SecondDate) - DATEDIFF(dd, @firstDate, @SecondDate) * 24) % 3600) + ':' + 
    CONVERT(VARCHAR(5), DATEDIFF(s, @firstDate, @SecondDate) % 3600 / 60)  + ':' + 
    CONVERT(VARCHAR(5), DATEDIFF(s, @firstDate, @SecondDate) % 60) AS Time_remain_In_Days

But it gives the problematic output if 

set @firstDate = '2013-12-17 15:35'
set @SecondDate = '2013-12-18 14:35'

It gives the output as 1 Days -1:0:0

But it should be as 0 Days 23:0:0

I have write the above code itself in view.

Please help me to modify the above query to get the desired output

Upvotes: 0

Views: 3604

Answers (2)

I A Khan
I A Khan

Reputation: 8839

Try this may be helpful:

If OBJECT_ID('UFN_HourMinuteSecond') Is Not Null
Drop Function dbo.UFN_HourMinuteSecond
Go
Exec(
'Create Function dbo.UFN_HourMinuteSecond
(
@StartDateTime DateTime,
@EndDateTime DateTime
) Returns Varchar(10) 
As
Begin

Declare @Seconds Int,
@Minute Int,
@Hour Int,
@Elapsed Varchar(10)

Select @Seconds = ABS(DateDiff(SECOND ,@StartDateTime,@EndDateTime))

If @Seconds >= 60 
Begin
select @Minute = @Seconds/60
select @Seconds = @Seconds%60

If @Minute >= 60
begin
select @hour = @Minute/60
select @Minute = @Minute%60
end

Else
Goto Final 
End

Final:
Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds =               IsNull(@Seconds,0)
select @Elapsed = Cast(@Hour as Varchar) + '':'' + Cast(@Minute as Varchar) + '':'' +     Cast(@Seconds as Varchar)

Return (@Elapsed)
End'
)

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

This should do it, provided you're using SQL Server 2008 or later:

declare @firstDate datetime
declare @SecondDate datetime
set @firstDate='2013-12-17T15:35:00'
set @SecondDate='2013-12-18T14:35:00'

select
    CONVERT(varchar(10),DATEDIFF(second,@firstDate,@secondDate)/86400)
        + ' days ' +
    CONVERT(varchar(10),CONVERT(time,
        DATEADD(second,DATEDIFF(second,@firstDate,@secondDate)%86400,0)))

For earlier versions, this should do:

select
    CONVERT(varchar(10),DATEDIFF(second,@firstDate,@secondDate)/86400)
        + ' days ' +
    CONVERT(varchar(10),
        DATEADD(second,DATEDIFF(second,@firstDate,@secondDate)%86400,0),108)

Upvotes: 5

Related Questions