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