Código SQL
[-]
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'')
DROP FUNCTION CalculaTiempo
GO
CREATE FUNCTION CalculaTiempo(@ValHoraIni as Numeric(4,2), @ValHoraFin as Numeric(4,2))
RETURNS Char(8)
AS
BEGIN
Declare
@HorIni Integer,
@MinIni Integer,
@HorFin Integer,
@MinFin Integer,
@TimeIni DateTime,
@TimeFin DateTime
Set @HorIni = ROUND(@ValHoraIni, 0, 1)
Set @MinIni = (@ValHoraIni - @HorIni) * 100
Set @HorFin = ROUND(@ValHoraFin, 0, 1)
Set @MinFin = (@ValHoraFin - @HorFin) * 100
Set @TimeIni = Convert(Datetime,'2008-01-01 '+ Cast(@HorIni As Char(2)) + ':' + Cast(@MinIni As Char(2)) + ':00.00',121)
Set @TimeFin = Convert(Datetime,'2008-01-01 '+ Cast(@HorFin As Char(2)) + ':' + Cast(@MinFin As Char(2)) + ':00.00',121)
Set @HorIni = Round(DateDiff(mi,@TimeIni, @TimeFin) / 60.0,0,1)
Set @MinIni = DateDiff(mi,@TimeIni, @TimeFin) - (@HorIni*60)
RETURN Case When @HorIni < 10 Then '0' Else '' End + RTrim(Cast(@HorIni As Char(2))) + ':' +
Case When @MinIni < 10 Then '0' Else '' End + RTrim(Cast(@MinIni As Char(2))) + ':00'
END
Código SQL
[-]
Select Database.Dbo.CalculaTiempo(10.40,18.55) As Tiempo