Q

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


This was first published in November 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