CODEWITHSUNDEEP
mysql
Blobert
Blobert

Reputation: 627

Returning Start and End Dates of Previous Quarter in MySQL

How would I go about writing a statement in MySQL to return the previous start and end date of the previous quarter? Say if I wanted the start and end date of the quarter before this one (i.e. beginning of last October and end of last December).

Upvotes: 6

Views: 5267

Answers (4)

AshClarke
AshClarke

Reputation: 3078

This will work such that if the date is in the first quarter, it'll roll back to the previous year in quarter 4.

SELECT @dateLastQuarter := DATE_SUB('yourdate', INTERVAL 3 MONTH);
SELECT MAKEDATE(YEAR(@dateLastQuarter), 1) + INTERVAL QUARTER(@dateLastQuarter) QUARTER - INTERVAL 1 QUARTER,
MAKEDATE(YEAR(@dateLastQuarter), 1) + INTERVAL QUARTER(@dateLastQuarter) QUARTER - INTERVAL 1 DAY;

Upvotes: 0

O. Jones
O. Jones

Reputation: 108651

I find it helpful to use this TRUNC_QUARTER stored function. It converts any date or date/time value into midnight on the first day of the calendar quarter in which it occurs. Then you can use INTERVAL arithmetic to manipulate it.

DELIMITER $$
DROP FUNCTION IF EXISTS TRUNC_QUARTER$$
CREATE
  FUNCTION TRUNC_QUARTER(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'returns preceding first of the quarter'
  RETURN DATE(CONCAT(YEAR(datestamp),'-', 1 + 3*(QUARTER(datestamp)-1),'-01'))$$
DELIMITER ;

With it you can write:

SELECT TRUNC_QUARTER(CURDATE()) - INTERVAL 1 QUARTER start_last_quarter,
       TRUNC_QUARTER(CURDATE()) - INTERVAL 1 DAY end_last_quarter

Upvotes: 1

C3roe
C3roe

Reputation: 96306

Begin and end of quarters are fixed, so there is no need to actually “calculate” those – we know they are January 1st, April 1st, etc, and March 31st, June 30th, etc. respectively.

So all that leaves us with is to get the correct year prepended to those fixed dates – if the current quarter is Q1, then go back one year, else use the current year.

QUARTER(NOW()) will give us the current quarter (from 1 to 4), so this could be as simple as this:

SELECT
  CASE QUARTER(NOW())
    WHEN 1 THEN DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y-10-01')
    WHEN 2 THEN DATE_FORMAT(NOW(), '%Y-01-01')
    WHEN 3 THEN DATE_FORMAT(NOW(), '%Y-04-01')
    WHEN 4 THEN DATE_FORMAT(NOW(), '%Y-07-01')
  END AS previous_quarter_begin,
  CASE QUARTER(NOW())
    WHEN 1 THEN DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y-12-31')
    WHEN 2 THEN DATE_FORMAT(NOW(), '%Y-03-31')
    WHEN 3 THEN DATE_FORMAT(NOW(), '%Y-06-30')
    WHEN 4 THEN DATE_FORMAT(NOW(), '%Y-09-30')
  END AS previous_quarter_end

http://sqlfiddle.com/#!9/9eecb/1858

If you want those dates in a different format, say mm/dd/yyyy instead, then you can simply change the format specified to 10/01/%Y etc.

(And if you want to test this, to see if it actually works correctly for different dates, then you can replace each occurrence of NOW() in the above query with a fixed date, like say '2015-12-22' to see if this will give the expected result for a date in December.)

Upvotes: 3

Felix Pamittan
Felix Pamittan

Reputation: 31879

Try this one:

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

Upvotes: 9

Related Questions