Q
Problem solve Get help with specific problems with your technologies, process and projects.

List of last seven dates

I am trying to create a query in MS Access that will generate a list of dates starting from today and ending seven days back. I have been able to get them in columns, but I want to get them in rows. Please note that I need the query to dynamically generate the data depending on today's date and I want to avoid creating tables.

I am trying to create a query in MS Access that will generate a list of dates starting from today and ending seven...

days back. I have been able to get them in columns, but I want to get them in rows. Please note that I need the query to dynamically generate the data depending on today's date and I want to avoid creating tables. Is this possible using just a query? Please help.

It's really easy if you have a special utility table which contains the numbers 0 through 7. I call this the integers table, and it has one column, called i.

select dateadd("d",-i,date()) as yourdate
  from integers
 where i between 0 and 7

For each value of i, that number of days is subtracted from today's date. The result is today's date and the previous seven dates.

You did indicate in your question that you wanted to avoid creating tables, but I urge you to create an integers table. Perhaps you wanted to avoid a table of hardcoded dates, which would, admittedly, be unwieldy. But an integers table comes in so handy in so many different scenarios, it really is worth it. See The integers table (28 January 2004) for an example which generates dates for an entire year.

This was last published in January 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close