How do I get the first date of a quarter in MySQL?

Question

The code that I have so far is below: I'm trying to find out what to put in place of my ?'s to find the start date of the quarter the date lies in.

SELECT
 QUARTER(r.callDate) AS quar,
 YEAR(r.callDate) AS ryear,
 ???????? AS scoreDateStart,
 (SELECT DATE (DATE_SUB( DATE_ADD( CONCAT( YEAR( r.callDate ), '-01-01'), INTERVAL QUARTER(r.callDate) QUARTER ), INTERVAL 1 DAY))) AS scoreDateEnd,
 group_concat(DISTINCT(r.resultId) separator ', ') AS resultIds
FROM results AS r
GROUP BY  quar, ryear
ORDER BY quar;

I have tried Googling but to no avail.

An example of the output would be:

'1', '2012', '2012-01-01', '2012-03-31', '57, 58, 59'
'2', '2012', '2012-04-01', '2012-06-30', '10549, 10551, 12598'

Answer 1

try this:

To get the start date of the current quarter use this:

  SELECT  MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER 
                                       - INTERVAL    1 QUARTER 

So your Query would be:

SELECT
 QUARTER(r.callDate) AS quar,
 YEAR(r.callDate) AS ryear,
 MAKEDATE(YEAR(r.callDate), 1) + INTERVAL QUARTER(r.callDate) QUARTER -
  INTERVAL 1 QUARTER  AS scoreDateStart,
 (SELECT DATE (DATE_SUB( DATE_ADD( CONCAT( YEAR( r.callDate ), '-01-01'), 
 INTERVAL QUARTER(r.callDate) QUARTER ), INTERVAL 1 DAY))) AS scoreDateEnd,
 group_concat(DISTINCT(r.resultId) separator ', ') AS resultIds
FROM results AS r
GROUP BY  quar, ryear
ORDER BY quar;