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?

    Requires Free Membership to View

    Login

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'

or:

 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


This was first published in December 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top