I am in a bit of a dilemma and cannot seem to figure out the solution in a "clean" fashion. Perhaps you can help?...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.