Solved

Select rows from last seven days

Posted on 2014-04-30
7
2,935 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 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

696 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