Solved

Select rows from last seven days

Posted on 2014-04-30
7
2,643 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
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 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 65

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now