Q

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'

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 last published in December 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close