Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: