在SQL中查找两个不同日期之间的HH：MM数

I am creating a turnaround time report in SQL for my job and need to be able to calculate the number of HH:MM between two different dates taking into account the DAYS as well as the HOURS. I seem to find a lot of answers that will bring back just the HOURS but need the DAYS as well.

我正在为我的工作在SQL中创建一个周转时间报告,并且需要能够计算两个不同日期之间的HH:MM的数量,同时考虑到DAYS和HOURS。我似乎找到了很多答案,这些答案只会带来HOURS,但也需要DAYS。

EX: Difference in HH:MM between '2017-04-18 16:03:00.000' and '2017-04-19 20:59:00.000'

EX:在“2017-04-18 16:03:00.000”和“2017-04-19 20:59:00.000”之间的HH:MM的差异

CURRENTLY attempting

CONVERT (CHAR(5),turn.[ADMIN DTTM]-turn.[VERIFY DTTM], 108) as 'VERIFIED to ADMINISTERED'

CONVERT(CHAR(5),turn。[ADMIN DTTM] -turn。[VERIFY DTTM],108)'VERIFIED to ADMINISTERED'

This is returning HH:MM but only for the "00:00:00:00" indicated: 04:56

这是返回HH:MM但仅适用于“00:00:00:00”表示:04:56

But what I need it to be returning is the combination of days and time: 28:56 OR 01:04:56

但我需要它回归的是天和时间的结合:28:56或01:04:56

Thanks

2 个解决方案

#1

Try this:

SELECT FORMAT([Minutes] / 1440, '#') + ':' 
  + FORMAT(([Minutes] % 1440) / 60, '00') + ':' 
  + FORMAT([Minutes] % 60, '00') AS [Result]
FROM
(
  SELECT DATEDIFF(mi, turn.[ADMIN DTTM], turn.[VERIFY DTTM]) AS [Minutes]
  FROM 
  (
    SELECT '2017-04-18 16:03:00.000' AS [ADMIN DTTM], '2017-04-19 20:59:00.000' AS [VERIFY DTTM]
  ) turn
) t

You will need to replace

你需要更换

(SELECT '2017-04-18 16:03:00.000' AS [ADMIN DTTM], '2017-04-19 20:59:00.000' AS [VERIFY DTTM])

(SELECT'2017-04-18 16:03:00.000'AS [ADMIN DTTM],'2017-04-19 20:59:00.000'AS [VERIFY DTTM])

with your table.

与你的桌子。

#2

You were taking the right approach by subtracting one value from the other. The trick is to use the FORMAT function to specify that you want the result in days, hours and minutes.

你通过从另一个中减去一个值来采取正确的方法。诀窍是使用FORMAT函数指定您希望结果的天数,小时数和分钟数。

SELECT FORMAT(turn.[VERIFY DTTM]-turn.[ORDER DTTM] - 1,'dd:HH:mm') as 'ORDERED to VERIFIED'

To understand why you need the "- 1" you need to understand what type of result you're getting back. If you just have:

要了解为什么需要“ - 1”,您需要了解您要获得的结果类型。如果你有:

SELECT turn.[VERIFY DTTM]-turn.[ORDER DTTM]

you'll see that the difference is returned as a datetime commencing from '1900-01-01 00:00:00.000'. Exactly one day's difference will give a result of '1900-01-02 00:00:00.000'. Internally datetime values are stored with the days as whole numbers and the time as decimals, so by subtracting one (day) you get the result you are after.

你会看到差异是从'1900-01-01 00:00:00.000'开始的日期时间返回的。正好一天的差异将给出'1900-01-02 00:00:00.000'的结果。内部日期时间值以天数作为整数存储,时间以小数形式存储,因此通过减去一天(天),您将得到您所追求的结果。

Two caveats though:

但有两点需要注意:

This will only work if the number of days difference is less than or equal to 31 (i.e. the number of days in January).

这只有在天数差小于或等于31(即1月份的天数)时才有效。

As presented above this will only work if there is at least one day's difference such as in your example.

如上所述,只有在您的示例中存在至少一天的差异时才会起作用。

If you want to cater for the possibility of less than a day's difference you can conditionally modify the FORMAT string accordingly.

如果您想要满足不到一天差异的可能性,您可以相应地有条件地修改FORMAT字符串。

SELECT FORMAT(turn.[VERIFY DTTM]-turn.[ORDER DTTM] - 1,IIF(turn.[VERIFY DTTM]-turn.[ORDER DTTM] < 1,'00','dd') + ':HH:mm') as 'ORDERED to VERIFIED'

#1