SQL Server related time range issues

I have an hour-by-hour database for N years. I want to extract a period of time in each year. For example, 2-3 21:00:00 to 5-12 09:00:00.

I want to query all the data in this time period for all the years at once. Is there a way to do this?

The table format is similar to this：

SQL Server seems to be limited to a year, month, day, or hour range. Trying to limit it with functions like MONTH or DAY doesn't give the desired result. It does work if you use a loop, but I'm still wondering if I can use some special way of solving this problem using only SQL set operation. Any ideas are appreciated. How can I do this?

The best way to do that is probably to construct a (possibly virtual) table of all the date ranges you want. For example, you can use GENERATE_SERIES or a VALUES constructor to generate the years, then calculate the dates off that.

SELECT
  t.*
FROM (VALUES
    (2020),
    (2021),
    (2022),
    (2023)
) AS v1(Year)
CROSS APPLY (
    SELECT
      StartDate = DATETIMEFROMPARTS(v1.Year, 2,  3, 21, 0, 0),
      EndDate   = DATETIMEFROMPARTS(v1.Year, 5, 12, 9,  0, 0)
) AS v2
JOIN YourTable t
  ON t.TT >= v2.StartDate
 AND t.TT < v2.EndDate;

db<>fiddle

Unfortunately, there's no good way to do this, where "good" is defined as "not needing a separate calculation for every row in the table", but at least it's possible.

Anyway, what I would do is use the default/0 year of 1900 to provide the target month/day/time value as a full datetime. Assuming the Month-Day ordering of the samples, that would look like this in code: 19000203 21:00:00 and 19000512 09:00:00 (for historical reasons, it's safer in SQL Server to use the unseparated ISO-8601 variant for dates).

Then I would transform the values for each record like this to match the year in each row:

DATEADD(Year, Year(TT)-1900, '19000203 21:00:00')
DATEADD(Year, Year(TT)-1900, '19000512 09:00:00')

So you can do a direct comparison:

SELECT *
FROM <table>
WHERE   TT >= DATEADD(Year, Year(TT)-1900, '19000203 21:00:00')
    AND TT <  DATEADD(Year, Year(TT)-1900, '19000512 09:00:00')

Again, I used 1900 because that's the epoch year for SQL Server, but other values can work as well. This isn't the fastest thing in the world, because you need to make a new calculation on each row, which probably also eliminates any index use, but at least it gives good results.

See it work here:

> https://dbfiddle.uk/t1hR5MAd