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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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.
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.
ASKER
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.
Thanks.
Please post your table schema (CREATE TABLE ), some sample data and expected result.
SELECT *
FROM tablea
WHERE CAST(DATEFIELD AS DATE) <= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
FROM tablea
WHERE CAST(DATEFIELD AS DATE) <= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
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...
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...
ASKER
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.