MySql Select past quarter to date

You can determine the quarter in PHP and use that in the query, I think.  Are your quarters the regular calendar business quarters, ending at the end of March, June, etc.?

select completedworkdate;
from yourtable;
where compledtedworkdate between beginquarterdate and lastquarterdate

I was looking for a way to do it within SQL, and not have to qeury the db, process with php, then re-query ...

one link I found ...
select ... FROM table WHERE quarter(mydate) = quarter(DATE_ADD(NOW(), INTERVAL -3 MONTH)) AND YEAR(mydate) = YEAR(NOW()) OR quarter(DATE_ADD(NOW(), INTERVAL -3 MONTH)) = 4 AND YEAR(mydate) = YEAR(NOW()) - 1

but i dont know what mydate is referring to...

Think I found one answer  myself... at least for current quarter but i have to add more rows to my table to test...

AND work.passed_date != "0000-00-00"
AND work.passed_date = quarter(NOW()) < quarter(curdate()) OR YEAR(NOW()) < YEAR(NOW())

Now to find how to choose last quarter...

I don't think you would have to query, process and requery.  Just throw the date in question at the function above and it will return an array that gives you the ISO8601 DATETIME fields of the boundary dates around the quarter when that date occurred.  Then you can use those fields in the query - so only one call is needed.

$datex = getQuarter('today');
$query = "SELECT thing FROM table WHERE passed_date BETWEEN '{$datex["FirstDate"]}' AND '{$datex["FirstDate"]}' ";

You might consider making this change to add a default date equal to today.  Line 7 of the code snippet above.

function getQuarter($datething='Today')

Select allOpen in new window

Sorry - the king of typos is at work again.  Make that query sequence more like the code snippet...

$query = "SELECT thing FROM table WHERE passed_date BETWEEN '{$datex["FirstDate"]}' AND '{$datex["LastDate"]}' ";

Select allOpen in new window

Just checking back - did you try the code example I posted?  It worked correctly for my queries.  Happy New Year, ~Ray

you guys rule.

But, I was looking to do this in sql :(

If I use php, I will award based on that decision :)

Thanks for your kind words.  In the original post you wrote,

"I need to run quarterly reports, quarter to date, or last quarter... couldnt get that to work... 500 points because I need it asap !"

So I gave you the solution that would give you the results ASAP.  If it is purely an academic learning exercise, feel free to experiment with SQL syntax - I expect that there is some kind of SQL statement that would have the same effect, but I didn't feel like taking the time to research it when I knew there was a 100% certainty that I could get it right in PHP with little effort.  That was to be responsive to your "asap" needs.

Best, ~Ray

12/28/09 03:19 PM, ID: 26133770 - this worked in my tests.