The_Happy_Pig Posted August 18, 2014 Share Posted August 18, 2014 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 More sharing options...
justsomeguy Posted August 18, 2014 Share Posted August 18, 2014 Your database should have date and time functions that you can use to build a date for a particular date and time. This is the list of functions for MySQL, for example, but it doesn't look like you're using that:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html Link to comment Share on other sites More sharing options...
The_Happy_Pig Posted August 18, 2014 Author Share Posted August 18, 2014 Thanks. I realize now that I wasn't very helpful either by not mentioning that the database is on MS SQL server 2012. Link to comment Share on other sites More sharing options...
justsomeguy Posted August 18, 2014 Share Posted August 18, 2014 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now