Selecting time portion of datetime columns

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

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.