Solved

Select rows from last seven days

Posted on 2014-04-30
7
3,038 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 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 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