Query to get logs between Start and End Night hour
Sample data

CREATE TABLE dbo.MyLogs
    (dtUtcDateTime datetime, HourPart int);

CREATE CLUSTERED INDEX cx 
ON dbo.MyLogs (HourPart, dtUtcDateTime);

INSERT INTO MyLogs
    (dtUtcDateTime, HourPart)
VALUES
    ('2015-07-31 19:00:06', 19),
    ('2015-07-31 19:00:07', 19),
    ('2015-07-31 19:01:27', 19),
    ('2015-07-31 19:01:29', 19),
    ('2015-07-31 19:01:50', 19),
    ('2015-07-31 19:04:17', 19),
    ('2015-07-31 19:04:42', 19),
    ('2015-07-31 19:27:48', 19),
    ('2015-07-31 19:48:17', 19),
    ('2015-07-31 20:04:43', 20),
    ('2015-08-01 01:55:11', 1),
    ('2015-08-01 01:55:13', 1),
    ('2015-08-01 01:55:21', 1),
    ('2015-08-01 01:55:23', 1),
    ('2015-08-01 01:55:25', 1),
    ('2015-08-01 01:55:29', 1),
    ('2015-08-01 01:55:42', 1),
    ('2015-08-01 01:55:44', 1),
    ('2015-08-01 01:55:45', 1),
    ('2015-08-01 01:56:26', 1),
    ('2015-08-01 01:56:27', 1),
    ('2015-08-01 01:56:31', 1),
    ('2015-08-01 01:56:32', 1),
    ('2015-08-01 02:06:32', 2),
    ('2015-08-01 02:10:49', 2),
    ('2015-08-01 02:14:36', 2),
    ('2015-08-01 02:15:01', 2),
    ('2015-08-01 02:15:10', 2),
    ('2015-08-01 02:15:11', 2),
    ('2015-08-01 02:15:16', 2),
    ('2015-08-01 02:15:20', 2),
    ('2015-08-01 03:11:40', 3),
    ('2015-08-01 03:11:38', 3),
    ('2015-08-01 03:14:07', 3),
    ('2015-08-01 03:14:20', 3),
    ('2015-08-01 03:14:12', 3),
    ('2015-08-01 03:14:17', 3),
    ('2015-08-01 03:14:51', 3),
    ('2015-08-01 03:15:04', 3),
    ('2015-08-01 03:15:06', 3),
    ('2015-08-01 03:15:10', 3),
    ('2015-08-01 03:15:11', 3),
    ('2015-08-01 03:15:16', 3),
    ('2015-08-01 03:15:24', 3),
    ('2015-08-01 03:15:25', 3),
    ('2015-08-01 03:15:29', 3),
    ('2015-08-01 03:15:41', 3),
    ('2015-08-01 03:15:43', 3),
    ('2015-08-01 03:17:24', 3),
    ('2015-08-01 03:17:25', 3),
    ('2015-08-01 03:17:28', 3),
    ('2015-08-01 03:17:49', 3),
    ('2015-08-01 03:17:45', 3),
    ('2015-08-01 03:19:30', 3),
    ('2015-08-01 03:20:03', 3),
    ('2015-08-01 03:22:12', 3),
    ('2015-08-01 03:23:13', 3),
    ('2015-08-01 03:23:16', 3),
    ('2015-08-01 03:24:46', 3),
    ('2015-08-01 03:26:36', 3),
    ('2015-08-01 03:26:35', 3),
    ('2015-08-01 03:26:49', 3),
    ('2015-08-01 03:26:52', 3),
    ('2015-08-01 03:26:57', 3),
    ('2015-08-01 03:27:14', 3),
    ('2015-08-01 03:27:15', 3),
    ('2015-08-01 03:27:31', 3),
    ('2015-08-01 03:27:30', 3),
    ('2015-08-01 03:28:04', 3),
    ('2015-08-01 03:29:16', 3),
    ('2015-08-01 03:29:32', 3),
    ('2015-08-01 03:29:30', 3),
    ('2015-08-01 03:29:43', 3),
    ('2015-08-01 03:29:56', 3),
    ('2015-08-01 03:30:06', 3),
    ('2015-08-01 03:30:25', 3),
    ('2015-08-01 03:30:26', 3),
    ('2015-08-01 03:30:27', 3),
    ('2015-08-01 03:32:21', 3),
    ('2015-08-01 03:34:07', 3),
    ('2015-08-01 03:35:01', 3),
    ('2015-08-01 03:35:02', 3),
    ('2015-08-01 03:35:04', 3),
    ('2015-08-01 03:35:41', 3),
    ('2015-08-01 03:35:48', 3),
    ('2015-08-01 03:36:03', 3),
    ('2015-08-01 03:36:02', 3),
    ('2015-08-01 03:36:08', 3),
    ('2015-08-01 03:36:09', 3),
    ('2015-08-01 03:36:24', 3),
    ('2015-08-01 03:36:33', 3),
    ('2015-08-01 04:30:06', 4),
    ('2015-08-01 04:36:33', 4),
    ('2015-08-01 06:03:12', 6),
    ('2015-08-01 06:03:24', 6),
    ('2015-08-01 06:03:26', 6),
    ('2015-08-01 06:03:29', 6),
    ('2015-08-01 06:03:30', 6),
    ('2015-08-01 06:04:16', 6),
    ('2015-08-01 06:25:25', 6),
    ('2015-08-01 06:25:33', 6),
    ('2015-08-01 06:26:31', 6),
    ('2015-08-01 06:26:42', 6),
    ('2015-08-01 06:26:47', 6),
    ('2015-08-01 06:26:49', 6),
    ('2015-08-01 06:26:53', 6),
    ('2015-08-01 06:28:10', 6),
    ('2015-08-01 06:28:11', 6),
    ('2015-08-01 06:28:12', 6),
    ('2015-08-01 06:31:26', 6),
    ('2015-08-01 06:32:02', 6),
    ('2015-08-01 06:32:03', 6),
    ('2015-08-01 06:32:08', 6),
    ('2015-08-01 06:32:39', 6),
    ('2015-08-01 06:32:42', 6),
    ('2015-08-01 06:32:47', 6),
    ('2015-08-01 06:33:55', 6),
    ('2015-08-01 06:37:11', 6),
    ('2015-08-01 06:38:11', 6),
    ('2015-08-01 06:38:21', 6),
    ('2015-08-01 06:38:32', 6),
    ('2015-08-01 06:38:31', 6),
    ('2015-08-01 06:38:38', 6),
    ('2015-08-01 06:38:39', 6),
    ('2015-08-01 06:38:40', 6),
    ('2015-08-01 06:38:43', 6),
    ('2015-08-01 06:38:59', 6),
    ('2015-08-01 06:39:40', 6),
    ('2015-08-01 06:41:19', 6),
    ('2015-08-01 06:41:23', 6),
    ('2015-08-01 06:41:33', 6),
    ('2015-08-01 06:41:35', 6),
    ('2015-08-01 06:41:38', 6),
    ('2015-08-01 06:42:53', 6),
    ('2015-08-01 06:44:15', 6),
    ('2015-08-01 06:44:47', 6),
    ('2015-08-01 06:45:18', 6),
    ('2015-08-01 06:45:22', 6),
    ('2015-08-01 06:46:15', 6),
    ('2015-08-01 06:46:16', 6),
    ('2015-08-01 06:49:28', 6),
    ('2015-08-01 06:50:26', 6),
    ('2015-08-01 06:51:08', 6),
    ('2015-08-01 06:55:17', 6),
    ('2015-08-01 06:58:38', 6),
    ('2015-08-01 06:58:46', 6),
    ('2015-08-01 06:59:18', 6),
    ('2015-08-01 07:01:16', 7),
    ('2015-08-01 07:01:20', 7),
    ('2015-08-01 07:01:24', 7),
    ('2015-08-01 07:01:59', 7),
    ('2015-08-01 07:02:02', 7),
    ('2015-08-01 07:02:11', 7),
    ('2015-08-01 07:02:13', 7),
    ('2015-08-01 07:03:55', 7),
    ('2015-08-01 07:04:16', 7),
    ('2015-08-01 07:04:26', 7),
    ('2015-08-01 07:04:27', 7),
    ('2015-08-01 07:04:28', 7),
    ('2015-08-01 07:05:11', 7),
    ('2015-08-01 07:05:13', 7),
    ('2015-08-01 07:06:31', 7),
    ('2015-08-01 07:06:32', 7),
    ('2015-08-01 07:07:21', 7),
    ('2015-08-01 07:07:39', 7),
    ('2015-08-01 07:07:32', 7),
    ('2015-08-01 07:07:33', 7),
    ('2015-08-01 07:07:36', 7),
    ('2015-08-01 07:08:51', 7),
    ('2015-08-01 07:08:52', 7),
    ('2015-08-01 07:10:03', 7),
    ('2015-08-01 07:11:04', 7),
    ('2015-08-01 07:11:05', 7),
    ('2015-08-01 07:11:11', 7),
    ('2015-08-01 07:11:17', 7),
    ('2015-08-01 07:11:35', 7),
    ('2015-08-01 07:12:15', 7),
    ('2015-08-01 07:14:31', 7),
    ('2015-08-01 07:14:43', 7),
    ('2015-08-01 07:15:29', 7),
    ('2015-08-01 07:16:05', 7),
    ('2015-08-01 07:16:07', 7),
    ('2015-08-01 07:17:25', 7),
    ('2015-08-01 07:17:39', 7),
    ('2015-08-01 07:19:13', 7),
    ('2015-08-01 07:27:46', 7),
    ('2015-08-01 07:28:04', 7),
    ('2015-08-01 07:28:10', 7),
    ('2015-08-01 07:28:12', 7),
    ('2015-08-01 07:29:17', 7),
    ('2015-08-01 07:31:36', 7),
    ('2015-08-01 07:31:45', 7),
    ('2015-08-01 07:31:48', 7),
    ('2015-08-01 07:31:52', 7),
    ('2015-08-01 07:31:54', 7),
    ('2015-08-01 07:31:55', 7),
    ('2015-08-01 07:32:01', 7),
    ('2015-08-01 07:32:08', 7),
    ('2015-08-01 07:32:12', 7),
    ('2015-08-01 07:33:00', 7);


Solution

The basic idea here is quite simple:


If @StartHour is before the @EndHour
Simply return rows 'between' those two values
If @EndHour is before the @StartHour:
Return rows less than @EndHour; and
Return rows greater than @StartHour


The logic can be implemented quite naturally as:

DECLARE 
    @StartHour integer = 1, -- or 18 
    @EndHour integer = 5; -- or 5

SELECT * 
FROM dbo.MyLogs AS ML
WHERE
    @StartHour &lt; @EndHour
    AND ML.HourPart &gt; @StartHour
    AND ML.HourPart &lt; @EndHour
UNION ALL
SELECT * 
FROM dbo.MyLogs AS ML
WHERE
    @StartHour &gt; @EndHour 
    AND ML.HourPart &gt; @StartHour
UNION ALL
SELECT * 
FROM dbo.MyLogs AS ML
WHERE
    @StartHour &gt; @EndHour 
    AND ML.HourPart &lt; @EndHour;


Execution plan



The Filters in this plan are start-up filters. At execution time, either the top branch or the two lower branches will be executed depending on the values of the local variables at that time. Splitting the query into three simple sections allows an index on HourPart to be used effectively.

Try it on the Stack Exchange Data Explorer

Or try the updated version that converts the supplied sample data to have the HourPart column as an indexed computed column.

If the start and end parameters were intended to be inclusive, simply change the &lt; and &gt; comparison operators to &lt;= and &gt;= respectively.