我想计算开始时间= 23:30和结束时间= 00:15之间的时间差，时间差即将到来-23.25 [英] I want to calculate time diff between starttime=23:30 and endtime=00:15 the time diff is coming -23.25

问题描述

我想计算时间差：

start_time  = 23:30
Finish_time = 00:15

时间差即将达到-23.15，而不是45分钟。

the time diff is coming -23.15 instead of 45 minutes.

我的代码：

DATEDIFF(minute, Start_Time, Finish_Time)

推荐答案

由于数据类型为时间，Sql服务器不知道您的意思是开始时间和结束时间在另一天。

这是解决此问题的一种方法：

Since the data type is Time, Sql server can't know you mean the start time and end time is in a different day.
Here is one way to solve this:

SELECT  CASE WHEN Start_Time > Finish_Time THEN
            DATEDIFF(minute, CAST(Start_Time As datetime), DATEADD(DAY, 1, CAST(Finish_Time As datetime)))
        ELSE
            DATEDIFF(minute, Start_Time, Finish_Time)
        END
FROM @T

如果您希望结果返回为时间，您可以改为：

If you want the result back as time, you can do this instead:

SELECT  CASE WHEN Start_Time > Finish_Time THEN
            CAST(DATEADD(DAY, 1, CAST(Finish_Time As datetime)) - CAST(Start_Time As datetime) as time)
        ELSE
            CAST(CAST(Finish_Time As datetime) - CAST(Start_Time As datetime) as time)
        END
FROM @T

< a href = http://rextester.com/CECDN12518 rel = nofollow noreferrer>查看有关rextester的实时演示

这里是一种方法获得以十进制表示的值，其中小数点的右侧是小时，而左侧是分钟：

Here is one way to get the values as decimal, where the right side of the decimal point is the hours and the left side is the minutes:

SELECT  CAST(
             REPLACE(
                     CONVERT (char(5), 
                              CASE WHEN Start_Time > Finish_Time THEN
                                CAST(DATEADD(DAY, 1, CAST(Finish_Time As datetime)) - CAST(Start_Time As datetime) as time)
                              ELSE
                                CAST(CAST(Finish_Time As datetime) - CAST(Start_Time As datetime) as time)
                              END
                     , 114)
             , ':', '.')
        as decimal(4,2))
FROM @T

最后编辑：

;WITH CTE AS
(
    SELECT  CASE WHEN Start_Time > Finish_Time THEN
                DATEDIFF(minute, CAST(Start_Time As datetime), DATEADD(DAY, 1, CAST(Finish_Time As datetime)))
            ELSE
                DATEDIFF(minute, Start_Time, Finish_Time)
            END As DiffInMinutes
    FROM @T
)
SELECT ((DiffInMinutes - (DiffInMinutes % 60)) / 60) + CAST((DiffInMinutes % 60) as float) / 60 As DiffAsDecimalHours
FROM CTE

这篇关于我想计算开始时间= 23:30和结束时间= 00:15之间的时间差，时间差即将到来-23.25的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！