Ask the Expert

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.

    Requires Free Membership to View

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

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: