asked on

How do I select arbitrary date ranges in a MySQL query

I need a query/code snippet that would allow me to get a date-range from the previous Friday night until now. I have figured out how to get date results from the last 7 days, but I need them specifically from last Friday.

On a slightly different note, but related to the same project, I need some code that would allow me to get results from each disparate month of the 12 months, starting with a year ago. For example, I need to return the number of orders within dates of July 1st - the 31st of 2008, August 1st - 31st of 2008, ... up to July 1st - 31st of 2009. This data will be fed into a dynamically created chart, so it has to all move along dynamically with the current date.

ASKER CERTIFIED SOLUTION

racek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

racek

or faster solution ...

SELECT *
FROM  your_table
WHERE LEFT(your_date_column,10) 
    = LEFT(CASE WEEKDAY(NOW()) 
   WHEN 4 THEN DATE_SUB(your_date_column, INTERVAL 7 days)
   WHEN 5 THEN DATE_SUB(your_date_column, INTERVAL 8 days)
   WHEN 6 THEN DATE_SUB(your_date_column, INTERVAL 9 days)
   WHEN 3 THEN DATE_SUB(your_date_column, INTERVAL 6 days)
   WHEN 2 THEN DATE_SUB(your_date_column, INTERVAL 5 days)
   WHEN 1 THEN DATE_SUB(your_date_column, INTERVAL 4 days)
   WHEN 0 THEN DATE_SUB(your_date_column, INTERVAL 3 days)
END , 10);

Open in new window

SOLUTION

racek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial