SQL:获取 DATEDIFF 不返回负值
我有一个查询,其中我正在提取可执行文件的运行时。 数据库包含其开始时间和结束时间。 我想获得跑步的总时间。 到目前为止,我已经:
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
当我执行此操作时,我得到了预期值,也得到了一些意外值。 例如,如果开始时间 = 2008-11-02 15:59:59.790 且结束时间 = 2008-11-02 19:05:41.857,则运行时间 = 4:-54。 对于这种情况,如何在 MS SQL SMS 中获取查询返回值 3:06?
谢谢。
我选择 Eoin Campbell 的答案作为最能满足我需求的答案。 David B 的也是可行的。
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?
Thanks.
Eoin Campbell's I selected as the answer is the most bulletproof for my needs. David B's is do-able as well.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
请尝试这些
假设有 2 个声明日期,
。 这将返回小时/分钟/秒
这是零填充的连接字符串版本
Try these
Assuming 2 declared dates.
This will return the hours / mins / seconds
This is the zero-padded concatenated string version
这是一种方法:
Here's a way to do it:
您需要与 datediff() 的调用保持一致。 它们都应该使用相同的 datepart 参数。
请参阅 MSDN 的 DATEDIFF (Transact-SQL) 文章。
在您的示例中,您同时使用“mi”和“hh”并连接。
选择您的持续时间的最小公分母(可能是 ss 或 s),并据此进行任何数学计算(正如其他答案所说明的那样,但并未真正描述)。
You need to be consistent with your calls to datediff(). They should all use the same datepart argument.
See MSDN's DATEDIFF (Transact-SQL) article.
In your example, you're using both "mi" and "hh" and concatenating.
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).
您应该将计算和表示逻辑分开:
如果您需要表示超过 24 小时,您将使用不同的表示逻辑。 这只是我能想到的最快的。
You should separate your calculation and presentation logic:
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.