/******************************************************************************
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
03:57 |
Category: |
0
comments
Comments (0)