/******************************************************************************
Purpose     : Get the Total hours with Minutes
Returns     : Decimal value
Arguments   : select dbo.UNF_GetHours(getdate(), getdate()+4)
===============================================================================Created By  : Suresh Kumar N
Created On  : 15-Mar-2011     
-------------------------------------------------------------------------------
Modification Log
-------------------------------------------------------------------------------
Modified By       Modified Date       Reason
-------------------------------------------------------------------------------

******************************************************************************/

create FUNCTION [dbo].[fnGetHours](@StartDate DATETIME, @EndDate DATETIME) 
RETURNS DECIMAL(8,2) 
AS 
BEGIN 
       DECLARE @TotalMinutes AS DECIMAL
       DECLARE @HoursWithoutSeconds AS INT
       DECLARE @Totalhours AS DECIMAL(8,2)
       -- Get the Total Minutes
       SELECT @TotalMinutes = CAST(DATEDIFF(mi,@StartDate,@EndDate)AS DECIMAL)
       -- Get the Hours without remaining Minutes
       select @HoursWithoutSeconds = FLOOR(CAST(@TotalMinutes AS DECIMAL)/60.0)
       --Calculate the Total hours with Minutes
       select @Totalhours =
              CAST(@HoursWithoutSeconds AS DECIMAL)
              +
              ((@TotalMinutes - (CAST(@HoursWithoutSeconds AS DECIMAL) * 60.0))/100)

 RETURN @Totalhours 
END 




Comments (0)