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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close