使DATEDIFF不返回负值

I have a query in which I am pulling the runtime of an executable. The database contains its start time and its end time. I would like to get the total time for the run. So far I have:

我有一个查询，我在其中提取可执行文件的运行时。数据库包含它的开始时间和结束时间。我想要得到跑步的总时间。到目前为止我有:

SELECT startTime, endTime,
cast(datediff(hh,starttime,endtime) as varchar)
+':'
+cast(datediff(mi,starttime,endtime)-60*datediff(hh,starttime,endtime) as varchar) AS RUNTIME
FROM applog
WHERE runID = 33871
ORDER BY startTime DESC

When I execute this I get expected values and also some unexpected. For example, if starttime = 2008-11-02 15:59:59.790 and endtime = 2008-11-02 19:05:41.857 then the runtime is = 4:-54. How do I get a quere in MS SQL SMS to return the value 3:06 for this case?

当我执行此操作时，会得到预期值和一些意外值。例如，如果starttime = -11-02 15:59 .790和endtime = -11-02 19:05:43 .857那么运行时= 4:-54。如何在MS SQL SMS中获得quere来返回值3:06 ?

Thanks.

谢谢。

Eoin Campbell's I selected as the answer is the most bulletproof for my needs. David B's is do-able as well.

约恩·坎贝尔的《我选择》是我最需要的防弹武器。大卫B的也可以。

4 个解决方案

#1

Try these

试试这些

Assuming 2 declared dates.

假设2声明日期。

declare @start datetime
set @start = '2008-11-02 15:59:59.790'

declare @end datetime
set @end = '2008-11-02 19:05:41.857'

This will return the hours / mins / seconds

这将返回小时/分钟/秒

select 
    (datediff(ss, @start, @end) / 3600), 
    (datediff(ss, @start, @end) / 60) % 60,
    (datediff(ss, @start, @end) % 60) % 60

--returns

----------- ----------- -----------
3           5           42

This is the zero-padded concatenated string version

这是零填充连接字符串版本

select
RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) / 3600)), 2) + ':' +
RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) / 60) % 60), 2) + ':' +
RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) % 60) % 60), 2)

--------
03:05:42

#2

Here's a way to do it:

这里有一个方法:

-- Find Hours, Minutes and Seconds in between two datetime
DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()

SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds

#3

You need to be consistent with your calls to datediff(). They should all use the same datepart argument.

您需要与对datediff()的调用保持一致。它们都应该使用相同的datepart参数。

See MSDN's DATEDIFF (Transact-SQL) article.

参见MSDN的DATEDIFF (Transact-SQL)文章。

In your example, you're using both "mi" and "hh" and concatenating.

在您的示例中，您同时使用“mi”和“hh”并进行连接。

Choose the least common denominator for your durations (probably ss or s) and do any math based on that (as the other answers are illustrating, but not really describing).

为你的持续时间选择最小公分母(可能是ss或s)，并根据它做任何计算(正如其他答案所示，但不是真正的描述)。

#4

You should separate your calculation and presentation logic:

你应该把你的计算和陈述逻辑分开:

DECLARE @applog TABLE
(
  runID int,
  starttime datetime,
  endtime datetime
)

INSERT INTO @applog (runID, starttime, endtime)
SELECT 33871, '2008-11-02 15:59:59.790', '2008-11-02 19:05:41.857'
-------------------
SELECT
  SUBSTRING(convert(varchar(30), DateAdd(mi, duration, 0), 121),
  12, 5) as prettyduration
FROM
(
SELECT starttime, DateDiff(mi, starttime, endtime) as duration
FROM @applog
WHERE runID = 33871
) as sub

If you need to represent more than 24 hours, you would use a different presentation logic. This is just what I could think of fastest.

如果您需要表示超过24小时，您将使用不同的表示逻辑。这就是我能想到的最快的。

#1