Hour, Minute & Second between two date

To calculate Hour(s), Minute(s) & Second(s) between two dates.

--Creating Function
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'
)

--Using the Function (Two Datetime Input parameters)
Select dbo.UFN_HourMinuteSecond ('2011-06-24 00:54:36.503', '2011-06-24 02:11:02.563') [Hour:Minute:Second]
Go

--Result

No comments:

Post a Comment

Post a Comment