Jump to content

Selecting from a date range with specific start/end times?


The_Happy_Pig

Recommended Posts

Hello all!

 

First time poster, and self taught newbie, so please forgive me! I've been plumbing the depths of this forum for a while now, but nothing I've been searching for gives me exactly what I'm after, so I thought it only polite to finally join up and ask.

 

I'm working on an incident management database and I've recently been asked to create some weekly performance reports. The table I'm looking at stores timestamps in dd/mm/yyyy hh:mm:ss format. I've used the getdate() function to return all incidents that were created within the last 7 days using the following (very basic) syntax:

 

SELECT INCIDENTS FROM TABLE WHERE TIMESTAMP1 > GETDATE() -7

 

Which works well enough, but as you all know, it returns results based on 7 days ago at the current time. The department runs 24 hours, with the first early shift coming in at 06:45, and I've very helpfully been told that it's not possible to automatically run the query at that time of the day.

 

What I'm looking for is a way to specify the start and finish times of the query; I want it to select everything that was created from 06:45:00 7 days ago and finish at 06:44:59 today without being date specific.

 

Any suggestions are gratefully received!

 

Thanks in advance.

 

Link to comment
Share on other sites

http://msdn.microsoft.com/en-us/library/ms186724.aspx#DateandTimeFunctionsIt looks like you can use DATETIMEFROMPARTS to build the date, and use the DAY, MONTH, and YEAR functions to get the parts of the current date. Build the date for the time for today, and subtract by 7 to get the same time a week earlier.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...