Selecting data between certain hours

I need to select data between an hours range. For example, I need to extract data between 4AM yesterday and 4AM today. Which date function, or combination, do I need to use? This is in SQL Server.

Thanks for mentioning the database system you use. It does make a difference, particularly when dealing with dates and times.

The first thing we need to do is find a way to represent "4AM today." In Microsoft SQL Server, the GETDATE() function returns the current date and time, so let's first strip off the current time:

convert(char(10),getdate(),120)

See the Microsoft documentation for CAST and CONVERT. Style 120 is the yyyy-mm-dd hh:mm:ss format, and converting to char(10) truncates the time off the end.

Now all we have to do is append 04:00:00 to the end of the date string, and CAST it back to datetime:

cast(convert(char(10),getdate(),120)
      + ' 04:00:00' as datetime)

We can get "4AM yesterday" simply by subtracting one day from "4AM today" using the DATEADD function. Thus your desired range will be:

select something
  from yourtable
 where somedate 
       between 
         dateadd(day,-1,
           cast(convert(char(10),getdate(),120)
               + ' 04:00:00' as datetime) )
       and
         cast(convert(char(10),getdate(),120)
             + ' 04:00:00' as datetime)

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.