Click here to Skip to main content
15,887,971 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How could return hh:mm
SQL
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
Posted
Updated 2-May-18 4:18am
v4

1 solution

1. Get the actual difference in seconds
SQL
SET @d = DATEDIFF(s, @start, @end)

2. Add the number of seconds to 0
SQL
DATEADD(s, @d, 0)


For instance for 123 seconds it will create 01/01/1900 00:02:03...
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900