0

I have a query which runs on particular dates. Whenever the query runs, it has to pick up the next quarter dates.

  • 1st quarter Jan-March
  • 2nd quarter April-June
  • 3rd quarter July-September
  • 4th quarter October- December

Suppose my query runs in April. I need to pick the data from July 1st to September 30th, without using the MySQL QUARTER function.

1
  • 3
    Why the restriction? What's wrong with using QUARTER()?
    – Andriy M
    Apr 2 '16 at 9:38
1
mysql> SELECT CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), '01') + INTERVAL
       3 * QUARTER(NOW()) + 1 - MONTH(NOW())
       MONTH;
--> 2016-07-01 

If you don't need the date of the beginning of the next quarter, but need the string '3rd quarter July-September', then use a CASE clause.

Without QUARTER:

SELECT CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), '01') + INTERVAL
       3 * FLOOR(MONTH(NOW())/3+1) + 1 - MONTH(NOW())
       MONTH;

Because QUARTER(x) is the same as FLOOR(MONTH(x)/3+1).

4
  • But this actually uses the QUARTER function?
    – dnoeth
    Apr 3 '16 at 11:00
  • Why no quarter? Added code.
    – Rick James
    Apr 3 '16 at 17:28
  • I don't mind using QUARTER, but the OP wanted Next quarter without using Quarter function, maybe he likes reinventing the wheel :)
    – dnoeth
    Apr 3 '16 at 17:45
  • And he wants us to do the inventing for him?
    – Rick James
    Apr 3 '16 at 20:00
0

NEXT QUARTER DATE IS:

SELECT CONCAT_WS('-', YEAR(NOW()), (QUARTER(NOW()) * 3) - 2 , '01') + INTERVAL 3 MONTH;

PREVIOUS QUARTER DATE IS:

SELECT CONCAT_WS('-', YEAR(NOW()), (QUARTER(NOW()) * 3) - 2 , '01') - INTERVAL 3 MONTH;

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .