Q:

TSQL using Time in Where Clause

I have a temperature measurements stored in a table, each measure have actuall date/time BUT logically the measure day runs from noon-noon (and not midnight-modnight).
For example a measure done at 09/18/2011 09:00:00 would belong to 09/18/2011 "measure day" but
measure done at 09/18/2011 13:00:00 already belongs to 09/19/2011 "measure day".
So now I want to do some stat queries, like the Average temp between 09-11 in each "Measure Day", this is done by:
-- Select the AVG temp between 09-11    
select MeasureDate, AVG(Temp) from @d
where Time between '09:00:00' and '11:00:00'
Group by MeasureDate

But if I want the Average temp between 23:00-01:00 I have to use different query (time "between" not working here):
-- Select the AVG temp between 23:00-01:00
select MeasureDate, AVG(Temp) from @d
where (Time>='23:00:00' or Time<='01:00:00')
Group by MeasureDate

Is there a simple way to have the same query to support both scenarios?
Ideally I want to have a function that get @from/@to as parameters and returns the result as table.
Below is a full example you can run.
Thx!
DECLARE @d TABLE ([Date] DATE, [Time] TIME(0), Temp DECIMAL(6,3), [MeasureDate] DATE); 

INSERT @d VALUES 
('2011-09-18','09:00:00',38.15, '2011-09-18'),
('2011-09-18','10:00:00',38.20, '2011-09-18'),
('2011-09-18','11:00:00',38.22, '2011-09-18'),
('2011-09-18','23:00:00',38.22, '2011-09-19'), -- Note the difference here between Date and     MeasureDate
('2011-09-19','00:00:00',38.17, '2011-09-19'),
('2011-09-19','01:00:00',38.32, '2011-09-19'),

('2011-09-19','09:00:00',39.15, '2011-09-19'),
('2011-09-19','10:00:00',39.20, '2011-09-19'),
('2011-09-19','11:00:00',39.22, '2011-09-19'),
('2011-09-19','23:00:00',39.22, '2011-09-20'), -- Note the difference here between Date and     MeasureDate
('2011-09-20','00:00:00',39.17, '2011-09-20'),
('2011-09-20','01:00:00',39.32, '2011-09-20');

-- Select the AVG temp between 09-11    
select MeasureDate, AVG(Temp) from @d
where Time between '09:00:00' and '11:00:00'
Group by MeasureDate

-- Select the AVG temp between 23:00-01:00
select MeasureDate, AVG(Temp) from @d
where (Time>='23:00:00' or Time<='01:00:00')
Group by MeasureDate

A:

Short (and inefficient) answer: preprocess your Time value (and your comparison value of course); "TIME" values roll around so you don't need to worry about adding or removing days:
SELECT MeasureDate, AVG(Temp) 
FROM @d
WHERE DateAdd(Hour, 12, [Time]) >= DateAdd(Hour, 12, Convert(Time, '09:00:00'))
    AND DateAdd(Hour, 12, [Time]) < DateAdd(Hour, 12, Convert(Time, '11:00:00'))
GROUP BY MeasureDate

This gives you consistent queries, although not particularly pretty of course. (you could preprocess the times somewhere else, no need to do it in SQL).
The more "correct" approach, in terms of performance (if you have or ever need an index on Time/MeasureTime that is), is to store a preprocessed "MeasureTime" and compare on that directly:
SELECT MeasureDate, AVG(Temp) 
FROM @d
WHERE MeasureTime >= DateAdd(Hour, 12, Convert(Time, '09:00:00'))
    AND MeasureTime < DateAdd(Hour, 12, Convert(Time, '11:00:00'))
GROUP BY MeasureDate

The "MeasureTime" could be maintained by client code, or as an indexed computed column, or maintained by a trigger - so many ways to further your pain!
Note, in these examples I've used the greater than and smaller than version rather than BETWEEN, because in my experience using BETWEEN on dates/times is always wrong.

