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：
| ID | SID | TT | BC |
|---|---|---|---|
| 1 | 55555 | 2007-01-01 00:00:00.000 | 22 12 18 20 |
| 2 | 55555 | 2007-01-01 01:00:00.000 | 22 12 18 20 |
| 3 | 55555 | 2009-02-01 01:00:00.000 | 22 12 18 20 |
| 4 | 55555 | 2009-03-21 20:00:00.000 | 22 12 18 20 |
| 5 | 55555 | 2011-04-02 23:00:00.000 | 22 12 18 20 |
| 6 | 55555 | 2012-07-05 01:00:00.000 | 11 65 10 61 |
| 7 | 55555 | 2013-08-01 02:00:00.000 | 16 20 6 16 |
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?
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: