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

SQL query to retervie data over last 6 days

Hi there,
I have been requested to run an automated query that runs once a week, for example on a Monday.  The query needs to extract the data for the last 7 days.  For example the date range for the first monday would of been 1 jan - 7 jan, the next time it runs the date range needs to be from 8 Jan - 14 Jan etc.  The date select needs to be done without using the parameter query.  The info is then displayed on a TV screen for the sales people to view.
Thanks you for your assistance
1 Solution
Hello habbotha,

Select * From MyTable Where MyDateField >= DateAdd(day,-7,convert(varchar(10),getdate(),120)) And MyDateField < convert(varchar(10),getdate(),120)

This will give you the 7 preceeding days, not including any data from today.


try the below attached code

This to return last 7 days
SELECT * FROM Table1 WHERE date_added >=dateadd(day,datediff(day,0,GetDate())- 7,0)
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

SELECT * FROM mytable WHERE RecordDate BETWEEN DATEADD(d,-7,getdate()) AND getdate()

This will return all records from exactly 7 days prior to the current date/time.  If the date is just a date value and you want the current day and the 6 preceding days then use.

SELECT * FROM mytable WHERE RecordDate BETWEEN CONVERT(varchar(10),dateadd(d,-6,getdate()),110) AND CONVERT(varchar(10),getdate(),110)
habbothaAuthor Commented:
Hi All:
Thanks for the info.  I wonder if you could assist me a bit futher with this same query.  Once i have run the query i need to count the number of entries per day.  the reason for this is that the info is going to be displayed in a graph.  I have tried using the group by function and i am nt having any luck there.
Thanks Heather
Assuming all your dates are just dates and no time values.

SELECT RecordDate, COUNT(*) FROM mytable WHERE RecordDate BETWEEN DATEADD(d,-7,getdate()) AND getdate() GROUP BY RecordDate
habbothaAuthor Commented:
thanks guys for the help - all is working
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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