How could return hh:mm
ALTER FUNCTION [dbo].[fn_GetTotal_Hour_and_Munites]
(
@DateFrom Datetime,
@DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)
SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
-(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
-CASE
WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
THEN 1
ELSE 0
END+CASE
WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
THEN 1
ELSE 0
END;
SET @TotalTimeDiff =
(
SELECT DATEDIFF(SECOND,
(
SELECT CONVERT(Time, @DateFrom)
),
(
SELECT CONVERT(Time, @DateTo)
)) %3600 /60
);
RETURN (SELECT(@TotalWorkDays *24*60*60) + @TotalTimeDiff)
END
==========================================
this function return only Minutes 00:23
==========================================
but DON'T tell to change to /3600.0
RETURN (SELECT(@TotalWorkDays * 24) + @TotalTimeDiff)
because it return second more then 59 Like 10:86
I just want max second to be 59
like 10:59
What I have tried:
I Tried to change
)) /3600
);
RETURN (SELECT(@TotalWorkDays *24) + @TotalTimeDiff)
================================================
this function return OUTPOT Hours only 10:00
================================================
how could return Hour with Minutes? 10:23 to be MAX Like 10:59 NOT 10:86