Ask the Expert

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?

    Requires Free Membership to View

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

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: