Calculate length of time

The following SQL user defined function is used to calculate the difference between two dates and returns number of days, hours and minutes.


/*Returns the number of days, hours, and minutes between dates.
Dates must be in YYYY MM DD HH MM format (There can be any signs between the characters, it doesn't have to be a space).

Dates are entered with the older date first, and the newer date second.

It handles leap years.

Example: dbo.funcCalcluateLengthOfTime('2004-01-01 10:00:00','2004-01-02 11:00:00') will return a value of: '1 Days 1 Hour 0 Minutes'

Example: dbo.funcCalcluateLengthOfTime('2004-01-01 10:00:00','2004-01-02 09:00:00') will return a value of: '0 Days 23 Hour 0 Minutes'

*/




CREATE FUNCTION [dbo].[funcCalculateLengthOfTime] (@OldDate as varchar(22), @NewDate as varchar(22)) RETURNS varchar(100)
AS
BEGIN

Declare @Result as varchar(100)
Declare @OldYear as varchar(4)
Declare @OldMonth as varchar(2)
Declare @OldDay as varchar(2)
Declare @NewYear as varchar(4)
Declare @NewMonth as varchar(2)
Declare @NewDay as varchar(2)
Declare @OldHour as varchar(2)
Declare @OldMin as varchar(2)
Declare @NewHour as varchar(2)
Declare @NewMin as varchar(2)
Declare @TempMonth as int
Declare @TempYear as int
Declare @ResultDay as int
Declare @ResultHour as int
Declare @ResultMin as int

set @ResultDay = 0
set @ResultHour = 0
set @ResultMin = 0
set @OldYear = substring(@OldDate,1,4)
set @OldMonth = substring(@OldDate,6,2)
set @OldDay = substring(@Olddate,9,2)
set @OldHour = substring(@OldDate,12,2)
set @OldMin = substring(@OldDate,15,2)
set @NewYear = substring(@NewDate,1,4)
set @NewMonth = substring(@NewDate,6,2)
set @NewDay = substring(@Newdate,9,2)
set @NewHour = substring(@NewDate,12,2)
set @NewMin = substring(@NewDate,15,2)
set @TempYear = convert(int,@OldYear)
set @TempMonth = convert(int,@OldMonth)

while @TempYear < resultday =" @ResultDay"> 0 or (@TempYear % 400) = 0) set @ResultDay = @ResultDay + 1
set @TempYear = @TempYear + 1
end

set @TempYear = convert(int,@OldYear)

while @TempMonth < tempmonth =" '01'" resultday =" @ResultDay" tempmonth =" '02'" resultday =" @ResultDay" tempmonth =" '03'" resultday =" @ResultDay" tempmonth =" '04'" resultday =" @ResultDay" tempmonth =" '05'" resultday =" @ResultDay" tempmonth =" '06'" resultday =" @ResultDay" tempmonth =" '07'" resultday =" @ResultDay" tempmonth =" '08'" resultday =" @ResultDay" tempmonth =" '09'" resultday =" @ResultDay" tempmonth =" '10'" resultday =" @ResultDay" tempmonth =" '11'" resultday =" @ResultDay" tempmonth =" '12'" resultday =" @ResultDay" tempmonth =" '02'"> 0 or (@TempYear % 400) = 0)) set @ResultDay = @ResultDay + 1
set @TempMonth = @TempMonth + 1
if @TempMonth = 13 set @TempYear = @TempYear + 1
if @TempMonth = 13 set @TempMonth = 01
end

if @OldMonth <> @NewMonth set @ResultDay = @ResultDay - convert(int,@OldDay)

if @NewMonth = @OldMonth and @NewYear = @OldYear set @ResultDay = convert(int,@NewDay) - convert(int,@OldDay)
if @NewMonth <> @OldMonth set @ResultDay = @ResultDay + convert(int,@NewDay)

if convert(int,@NewHour) > convert(int,@OldHour) set @ResultHour = convert(int,@NewHour) - convert(int,@OldHour)
if convert(int,@NewHour) < resulthour =" 24"> convert(int,@OldMin) set @ResultMin = convert(int,@NewMin) - convert(int,@OldMin)
if convert(int,@NewMin) < resultmin =" 60" resulthour =" @ResultHour" resultday =" @ResultDay" resultday =" @ResultDay" resultday =" 1" resulthour =" 1" resultmin =" 1" result =" convert(varchar(100),@ResultDay)" resultday =" 1" resulthour =" 1"> 1 set @Result = convert(varchar(100),@ResultDay) + ' Day ' + convert(varchar(100),@ResultHour) + ' Hour ' + convert(varchar(100),@ResultMin) + ' Minutes'
if @ResultDay = 1 and @ResultHour > 1 and @ResultMin = 1 set @Result = convert(varchar(100),@ResultDay) + ' Day ' + convert(varchar(100),@ResultHour) + ' Hours ' + convert(varchar(100),@ResultMin) + ' Minute'
if @ResultDay = 1 and @ResultHour > 1 and @ResultMin > 1 set @Result = convert(varchar(100),@ResultDay) + ' Day ' + convert(varchar(100),@ResultHour) + ' Hours ' + convert(varchar(100),@ResultMin) + ' Minutes'

if @ResultDay > 1 and @ResultHour = 1 and @ResultMin = 1 set @Result = convert(varchar(100),@ResultDay) + ' Days ' + convert(varchar(100),@ResultHour) + ' Hour ' + convert(varchar(100),@ResultMin) + ' Minute'
if @ResultDay > 1 and @ResultHour = 1 and @ResultMin > 1 set @Result = convert(varchar(100),@ResultDay) + ' Days ' + convert(varchar(100),@ResultHour) + ' Hour ' + convert(varchar(100),@ResultMin) + ' Minutes'
if @ResultDay > 1 and @ResultHour > 1 and @ResultMin = 1 set @Result = convert(varchar(100),@ResultDay) + ' Days ' + convert(varchar(100),@ResultHour) + ' Hours ' + convert(varchar(100),@ResultMin) + ' Minute'
if @ResultDay > 1 and @ResultHour > 1 and @ResultMin > 1 set @Result = convert(varchar(100),@ResultDay) + ' Days ' + convert(varchar(100),@ResultHour) + ' Hours ' + convert(varchar(100),@ResultMin) + ' Minutes'

if @ResultDay = 1 and @ResultHour = 0 and @ResultMin = 1 set @Result = convert(varchar(100),@ResultDay) + ' Day ' + convert(varchar(100),@ResultMin) + ' Minute'
if @ResultDay = 1 and @ResultHour = 1 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultDay) + ' Day ' + convert(varchar(100),@ResultHour) + ' Hour'
if @ResultDay = 1 and @ResultHour = 0 and @ResultMin > 1 set @Result = convert(varchar(100),@ResultDay) + ' Day ' + convert(varchar(100),@ResultMin) + ' Minutes'
if @ResultDay = 1 and @ResultHour > 1 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultDay) + ' Day ' + convert(varchar(100),@ResultHour) + ' Hours'

if @ResultDay > 1 and @ResultHour = 0 and @ResultMin = 1 set @Result = convert(varchar(100),@ResultDay) + ' Days ' + convert(varchar(100),@ResultMin) + ' Minute'
if @ResultDay > 1 and @ResultHour = 1 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultDay) + ' Days ' + convert(varchar(100),@ResultHour) + ' Hour'
if @ResultDay > 1 and @ResultHour = 0 and @ResultMin > 1 set @Result = convert(varchar(100),@ResultDay) + ' Days ' + convert(varchar(100),@ResultMin) + ' Minutes'
if @ResultDay > 1 and @ResultHour > 1 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultDay) + ' Days ' + convert(varchar(100),@ResultHour) + ' Hours'

if @ResultDay = 0 and @ResultHour = 1 and @ResultMin = 1 set @Result = convert(varchar(100),@ResultHour) + ' Hour ' + convert(varchar(100),@ResultMin) + ' Minute'
if @ResultDay = 0 and @ResultHour = 1 and @ResultMin > 1 set @Result = convert(varchar(100),@ResultHour) + ' Hour ' + convert(varchar(100),@ResultMin) + ' Minutes'
if @ResultDay = 0 and @ResultHour > 1 and @ResultMin = 1 set @Result = convert(varchar(100),@ResultHour) + ' Hours ' + convert(varchar(100),@ResultMin) + ' Minute'
if @ResultDay = 0 and @ResultHour > 1 and @ResultMin > 1 set @Result = convert(varchar(100),@ResultHour) + ' Hours ' + convert(varchar(100),@ResultMin) + ' Minutes'

if @ResultDay = 0 and @ResultHour = 0 and @ResultMin = 1 set @Result = convert(varchar(100),@ResultMin) + ' Minute'
if @ResultDay = 0 and @ResultHour = 0 and @ResultMin > 1 set @Result = convert(varchar(100),@ResultMin) + ' Minutes'
if @ResultDay > 1 and @ResultHour = 0 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultDay) + ' Days'
if @ResultDay = 1 and @ResultHour = 0 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultDay) + ' Day'
if @ResultDay = 0 and @ResultHour = 1 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultHour) + ' Hour'
if @ResultDay = 0 and @ResultHour > 1 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultHour) + ' Hours'
if @ResultDay = 0 and @ResultHour = 0 and @ResultMin = 0 set @Result = convert(varchar(100),@ResultMin) + ' Minutes'

if @Result is null set @Result = 'The first date is older than the second date.'

Return @Result
END