Unless I misunderstand your question, you can simply test the hour part of the datetime column using the DATEPART function:
select columns from yourtable where DATEPART(hour,DateTimeAligned) >= 7 and DATEPART(hour,DateTimeAligned) < 15
This will give you times from 07:00:00 up to and including 14:59:59. If you want to include 15:00:00 (3:00 p.m.) but not 15:00:01, that's a bit trickier:
where CONVERT(char(8),DateTimeAligned,108) >= '07:00:00' and CONVERT(char(8),DateTimeAligned,108) <= '15:00:00'
where CONVERT(char(8),DateTimeAligned,108) between '07:00:00' and '15:00:00'
Style 108 is HH:MM:SS; see CAST and CONVERT in the Microsoft documentation.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in December 2002