Sql ..如何用分钟计算DATEDIFF小时 [英] Sql .. How to calculate DATEDIFF hour with minutes

问题描述

如何返回hh：mm

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

=================================== =======

此功能仅返回分钟00:23

=================== =======================

但不要告诉改为/3600.0

RETURN （选择（@TotalWorkDays * 24）+ @TotalTimeDiff）



因为它再回到第二名然后59比如10:86

我只想要最大秒数为59

如同10:59



我尝试过的事情：



我试图改变



））/ 3600

）;



返回（选择（@TotalWorkDays * 24）+ @TotalTimeDiff）

================== ==============================

此功能返回OUTPOT小时o nly 10:00

====================================== ==========

怎么能用分钟返回小时？ 10:23成为MAX喜欢10:59不是10:86

==========================================
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

推荐答案

1。以秒为单位获取实际差异

1. Get the actual difference in seconds

SET @d = DATEDIFF(s, @start, @end)

2.将秒数加到0

2. Add the number of seconds to 0

DATEADD(s, @d, 0)

例如，123秒它将创建01/01/1900 00:02:03 ......

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

这篇关于Sql ..如何用分钟计算DATEDIFF小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！