• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4046
  • Last Modified:

Select rows from last seven days

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
newtoperlpgm
Asked:
newtoperlpgm
1 Solution
 
Anthony PerkinsCommented:
Something like this perhaps:
SELECT  *
FROM    tablea
WHERE   DATEFIELD >= DATEADD(DAY, -7, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0
 
newtoperlpgmAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
newtoperlpgmAuthor Commented:
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
 
Anthony PerkinsCommented:
Please post your table schema (CREATE TABLE ), some sample data and expected result.
0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT  *
FROM    tablea
WHERE   CAST(DATEFIELD AS DATE) <= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
0
 
ValentinoVBI ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now