如何在两个日期之间显示小时和分钟

I am having a table consists of to datetime columns "StartTime" and "CompleteTime". Initially completeTime column will be NULL untill the process is completed. And now my requirement is to display hours and minutes as shown Below

我有一个表包含日期时间列“StartTime”和“CompleteTime”。最初，completeTime列将为NULL，直到该过程完成。现在我的要求是显示小时和分钟，如下所示

Output: Ex: 2:01 Hr (This Means "2" represents hours and "01" represents minutes)

输出：例如：2：01 Hr（这意味着“2”代表小时，“01”代表分钟）

I Tried as below:

我试过如下：

Declare @StartDate dateTime = '2016-03-31 04:59:11.253'
Declare @EndDate dateTime = GETUTCDATE()

SELECT REPLACE(CONVERT(VARCHAR,CAST(DATEDIFF(second, @StartDate, 
ISNULL(GETUTCDATE(),@EndDate)) / 36000.0 AS DECIMAL(9,2))) + ' hr','.',':')

Output:

输出：

0:05 hr

Required Output:

要求输出：

0:32 hr

Note: Hi while giving negative marking check my query once. I already referred some link's related to this but it won't work.

注意：您好，同时给出负标记，请检查我的查询一次。我已经提到了一些与此相关的链接，但它不起作用。

3 个解决方案

#1

Try this

尝试这个

DECLARE @STARTDATE DATETIME = '2016-03-31 04:59:11.253'
DECLARE @ENDDATE DATETIME = GETUTCDATE()

SELECT CONVERT(VARCHAR(10),DATEDIFF(MINUTE, @STARTDATE, @ENDDATE)/60)+':'+CONVERT(VARCHAR(10),DATEDIFF(MINUTE, @STARTDATE, @ENDDATE)%60)+' hr' AS DIFF

Result:

结果：

Diff
0:52 hr

Diff more than 24 hour also will handle this

差异超过24小时也会处理这个问题

72:56 hr

#2

try this (MS SQL query) -

试试这个（MS SQL查询） -

Declare @StartDate dateTime = '2016-03-31 04:59:11.253'
Declare @EndDate dateTime = GETUTCDATE()

SELECT CONVERT(varchar(5), 
   DATEADD(minute, DATEDIFF(minute, @StartDate, @EndDate), 0), 114) + ' hr'

Result - 00:47 hr

结果 - 00:47小时

#3

Declare @StartDate dateTime = '2016-03-31 04:59:11.253'

SELECT CONVERT(varchar(10),DATEADD(minute, DATEDIFF(minute, @StartDate,GETUTCDATE()), 0), 114) + ' Hr'

Returns: '0:43 Hr'

返回：'0:43 Hr'

Your ISNULL(GETUTCDATE(),@EndDate) isn't doing anything since GETUTCDATE() will never be NULL, and you were dividing by 36000 instead of 3600, but the primary issue with your query is that you were putting a decimal value into a sexagesimal container (60 minutes/hour). Ie: 90 seconds should be 1:30 instead of :9

您的ISNULL（GETUTCDATE（），@ EndDate）没有做任何事情，因为GETUTCDATE（）永远不会为NULL，而您除以36000而不是3600，但您的查询的主要问题是您将十进制值放入一个六十分钟的容器（60分钟/小时）。即：90秒应该是1:30而不是：9

Edit: Mixup in my initial query, the DATEADD() method is cleaner.

编辑：在我的初始查询中混合，DATEADD（）方法更干净。

#1