Q

SQL to select only certain times within a date range

I have a field in my table which stores dates and times in datetime format. I want to get the data from 10 p.m. to 6 a.m. between 1st July and 31st July. What's the best way to get this data?

I have a field in my table which stores dates and times in datetime format. I want to get the data from 10 p.m.

to 6 a.m. between 1st July and 31st July. What's the best way to get this data?

This is one of the few times where having separate DATE and TIME columns would be beneficial.

select columns
  from table
 where datecol between '2007-07-01' and '2007-07-31'
   and (
       timecol >= '22:00'
    or timecol <= '06:00'
       )

With a DATETIME column, however, it's a little trickier. We need to "extract" the time portion of the DATETIME values. The query would look something like this:

select columns
  from table
 where datetimecol >= '2007-07-01' 
   and datetimecol  < '2007-08-01'
   and (
       extract(hour from datetimecol) >= 22
    or extract(hour_minute from datetimecol) <= '06:00'
       )

Exact syntax will depend on which database system you're using. Notice how the range test for the dates uses an open-ended interval.

This was first published in August 2007

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close