I am in a bit of a dilemma and cannot seem to figure out the solution in a "clean" fashion. Perhaps you can help? I have a SQL Server 2000 Database with a field called DateTimeAligned of type DateTime. Since all values in this field are full date/time values, I have no way of searching for time specific values. In my case, I may have 10,000 records for the last six months of data. Of which I want to return only the records that are between say 7:00 am and 3:00 pm regardless of the date value. So far my only solution is to go to my start date and begin to build a string that has the date and time explicitly specified for every day in my search range (in the case of six months that would be approx. 6 x 30 x 2 (6 months, 30 days per month, start and end times) clauses long. This is unacceptable for me especially if I need to get the last two years worth of records. Any ideas?
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.