?
Solved

Select rows from last seven days

Posted on 2014-04-30
7
Medium Priority
?
3,145 Views
Last Modified: 2014-05-05
Hi,
I have a SQL Server database table that I want to select rows from for the last seven days.  I use the date field, but it appears that my query isn't working correctly, I may be missing records from today, the day I actually run the query.  Here is my query

select * from tablea
where DATEFIELD > DATEADD(DAY, -7, GETDATE())
AND DATEFIELD < DATEADD(DAY, +1 GETDATE())

Thanks for your help.
0
Comment
Question by:newtoperlpgm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 40032608
Something like this perhaps:
SELECT  *
FROM    tablea
WHERE   DATEFIELD >= DATEADD(DAY, -7, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0
 

Author Comment

by:newtoperlpgm
ID: 40032629
Hi Anthony Perkins,
What does this query do, I am reading up on and trying to understand DATEDIFF.  Does it get the data between today and 7 days ago, or now and 7 days ago?  What if the query runs now at noon, will it yield all records until noon?  If I run this query next week, will it yield the records from noon on?  Thanks.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40032651
>select rows from for the last seven days.
This assumes that there is a date column somewhere.  If that does not exist, then you are likely out of luck here, as SQL does not store on its own when a row was inserted.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

Author Comment

by:newtoperlpgm
ID: 40032864
Specifically, what I'd like to do it run the script on a Monday, and have it return the records from last Monday thru Sunday (which would be yesterday if I ran it on Mondays.)
Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40033046
Please post your table schema (CREATE TABLE ), some sample data and expected result.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 40036458
SELECT  *
FROM    tablea
WHERE   CAST(DATEFIELD AS DATE) <= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40037203
what I'd like to do it run the script on a Monday, and have it return the records from last Monday thru Sunday (which would be yesterday if I ran it on Mondays.)

Do you always want data from the previous Monday to Sunday period, regardless of when the query is run?  So if you'd run it on a Wednesday, it should still produce data starting previous Monday up until previous Sunday?  Or would you in that case expect data from previous Wednesday up until previous Tuesday (which would be yesterday too in that case)?

The implementation of the query really differs depending on how you're going to answer above questions.

Also, as Anthony asked, examples do really help to clarify requirements...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question