How to get the first quarter date in MySQL?

Question

The code I still have is below: I'm trying to figure out what to use instead of me? to find the start date of the quarter in which the date is located.

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 tried Googling, but to no avail.

Output Example:

 '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 the following:

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 will look like this:

 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;