如何将午夜（00）的军事时间DATEPART小时转换为在需要进行计算时可以使用的值？ [英] How to convert a DATEPART hour that's military time for midnight (00) to a value I can use when I need it for a calculation?

问题描述

我有要写入存储过程的这段代码：

I have this code which I'm writing into a stored procedure:

declare @StartTime time
declare @EndTime time
declare @Temp_StartTime time

declare @temp_StartHour int
declare @temp_EndHour int
declare @temp_StartMinute int
declare @temp_EndMinute int

SET @StartTime='22:30:00'
SET @EndTime='00:52:00'
SET @Temp_StartTime=@StartTime

SET @temp_StartHour=DATEPART(HOUR, @StartTime)
SET @temp_EndHour=DATEPART(HOUR, @EndTime)
SET @temp_StartMinute=DATEPART(MI, @StartTime)
SET @temp_EndMinute=DATEPART(MI, @EndTime)

if(@temp_EndMinute>0)
    BEGIN
        SET @temp_EndHour=@temp_EndHour+1
    END

DECLARE @Temp_Table TABLE
(
  StartHour int,
  StartMinute int,
  EndHour int,
  EndMinute int,
  StartTime time,
  EndTime time
)

WHile((@temp_EndHour-@temp_StartHour>=1))
    BEGIN
        INSERT INTO @Temp_Table
        SELECT (DATEPART(HOUR, @Temp_StartTime)) AS StartHour,(DATEPART(MINUTE, @Temp_StartTime)) AS StartMinute,
        @temp_StartHour+1 AS EndHour, 
        0 AS EndMinute, @StartTime as StartTime, @EndTime as EndTime

        SET @temp_StartHour=@temp_StartHour+1
        SET @Temp_StartTime=DATEADD(HOUR,1,@Temp_StartTime)

        if(DATEPART(MI, @Temp_StartTime)!=0)
            BEGIN
                SET @Temp_StartTime=DATEADD(MI,-@temp_StartMinute,@Temp_StartTime)
            END
    END

SELECT * FROM @Temp_Table 

如果您使用任何时间值，效果都很好除了00:52:00的例子以外，我还在那里。例如，如果EndTime为23:05，则存储过程的效果很好。我对DATEPART进行了一些研究，但没有发现任何有关如何正确计算其在军事时间午夜的信息。

It works great if you use any time value other than the 00:52:00 example I have up there. For instance, if EndTime was 23:05, the stored procedure works great. I did some research around DATEPART but didn't find anything helpful as to how to get it to calculate midnight at military time properly.

编辑：当代码正常运行时，它将计算开始和结束时间之间的小时数，其想法是将每个小时的新行存储到临时文件中表（最终将其保存到新表中，以按小时跟踪停机）。当我在21:30到22:15运行它时，可以找到它。我得到两行，分别反映了21:00至22:00和22:00至23:00（这是我想要的逻辑）。但是，在午夜时分将军人扔在那里，由于没有计算出00，所以我没有返回任何行。

When the code runs properly, it calculates the time in how many hours between start and end time and the idea is to store new rows for each hour into the temp table (eventually this is going to be saved to a new table for tracking outages by hour). It works find when I run it with 21:30 to 22:15. I get two rows reflecting 21:00 to 22:00 and 22:00 to 23:00 (this is the logic I want). But throw military midnight in there, and I get no rows returned as the calc won't compute the 00.

我在数据库中发现了一些示例，这些示例显示了开始时间为22：00：0000，结束时间为00：00：00.0000000，然后反之亦然。因此，一种计算方式将以开始时间为00，但如果开始时间为21：00：0000，结束时间为00：52：0000，则没有骰子。我没有返回任何行。

I have found examples in my database that show start times of 22:00:0000 and end times of 00:00:00.0000000 and then visa versa. So one way WILL calculate, where start time is 00, but if start time is 21:00:0000 and end time is 00:52:0000 then no dice. I get no rows returned.

推荐答案

如果我没有丢失任何内容，那么可以尝试以下方法，而不是循环： / p>

If I am not missing anything, this is what you could try instead of your loop:

DECLARE
  @StartTime time,
  @EndTime   time;

SET @StartTime = '22:30:00';
SET @EndTime   = '00:52:00';

WITH timerange AS (
  SELECT
    StartTime = CAST(@StartTime AS datetime),
    EndTime   = DATEADD(
      DAY,
      CASE WHEN @StartTime > @EndTime THEN 1 ELSE 0 END,
      CAST(@EndTime AS datetime)
    )
),
hourly AS (
  SELECT
    n.number,
    t.StartTime,
    t.EndTime,
    HStart = DATEADD(HOUR, DATEDIFF(HOUR, 0, t.StartTime) + n.number    , 0),
    HEnd   = DATEADD(HOUR, DATEDIFF(HOUR, 0, t.StartTime) + n.number + 1, 0)
  FROM timerange t
    INNER JOIN master..spt_values n
      ON n.number BETWEEN 0 AND DATEDIFF(HOUR, t.StartTime, t.EndTime)
  WHERE n.type = 'P'
),
hourly2 AS (
  SELECT
    number,
    HStart = CASE WHEN StartTime > HStart THEN StartTime ELSE HStart END,
    HEnd   = CASE WHEN EndTime   < HEnd   THEN EndTime   ELSE HEnd   END
  FROM hourly
)
SELECT
  StartHour   = DATEPART(HOUR  , HStart),
  StartMinute = DATEPART(MINUTE, HStart),
  EndHour     = DATEPART(HOUR  , HEnd  ),
  EndMinute   = DATEPART(MINUTE, HEnd  ),
  StartTime   = CAST(HStart AS time),
  EndTime     = CAST(HEnd   AS time)
FROM hourly2
ORDER BY number
;

产生的输出是这样的：

StartHour   StartMinute EndHour     EndMinute   StartTime        EndTime
----------- ----------- ----------- ----------- ---------------- ----------------
22          30          23          0           22:30:00.0000000 23:00:00.0000000
23          0           0           0           23:00:00.0000000 00:00:00.0000000
0           0           0           52          00:00:00.0000000 00:52:00.0000000

这篇关于如何将午夜（00）的军事时间DATEPART小时转换为在需要进行计算时可以使用的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！