I’m trying to get the start/end dates of a non-standard quarter using MySQL. This question/answer works great to get the start/end dates of standard quarters (Jan-Mar, Apr-Jun, etc.).
Any suggestions on how to modify the query if the quarter does not start on a typical fiscal year’s quarters (Jan-Mar, Apr-Jun, etc.)? I have a case where I need the quarter to begin on a February (Feb-Apr, May-Jul, etc.) as well as beginning in March (Mar-May, Jun-Aug, etc.).
I have tried adjusting intervals to accommodate the shift in the months by 1 or 2, but because MySQL returns a set 1,2,3,4 for QUARTER() using a standard year’s quarters, it’s not working correctly.
Here are the queries from the previously linked question that return a start/end on a standard calendar year’s quarters (passing in a date I’m interested in getting the start/end of the quarter it is in):
SELECT MAKEDATE(YEAR('2022-11-02'), 1) + INTERVAL QUARTER('2022-11-02') QUARTER - INTERVAL 1 QUARTER AS quarter_start;
+---------------+
| quarter_start |
+---------------+
| 2022-10-01 |
+---------------+
SELECT MAKEDATE(YEAR('2022-11-02'), 1) + INTERVAL QUARTER('2022-11-02') QUARTER - INTERVAL 1 DAY AS quarter_end;
+---------------+
| quarter_end |
+---------------+
| 2022-12-31 |
+---------------+
How can I adjust this to return a quarter_start of 2022-11-01 and quarter_end of 2023-01-31 if the quarter should be shifted forward by 1 month (quarters: Feb-Apr, May-Jul, Aug-Oct, Nov-Jan)?
2
Answers
You’ll have to calculate it yourself from the month.
This is simple date math.
where offset is 2 for calendar quarters, 1 for quarters beginning in feb/may/aug/nov, 0 for quarters beginning in mar/jun/sep/dec.