Last active
December 10, 2015 23:28
-
-
Save GregPK/4509179 to your computer and use it in GitHub Desktop.
Two functions that return the date of the start and end of the quarter of which the given date is in. (+some "unit tests").
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DELIMITER $$ | |
/** qStart (DATE date) | |
* Get the date of the start of a quarter given a date in that quarter. | |
*/ | |
DROP FUNCTION IF EXISTS `qStart`$$ | |
CREATE FUNCTION `qStart`(d DATE) RETURNS DATE | |
DETERMINISTIC | |
BEGIN | |
RETURN d - INTERVAL MOD(MONTH(d)-1,3) MONTH - INTERVAL DAY(d)-1 DAY; | |
END$$ | |
/** qEnd (DATE date) | |
* Get the date of the end of a quarter given a date in that quarter. | |
* Please note that this could be simplified if you use have the qStart function | |
*/ | |
DROP FUNCTION IF EXISTS `qEnd`$$ | |
CREATE FUNCTION `qEnd`(d DATE) RETURNS DATE | |
DETERMINISTIC | |
BEGIN | |
DECLARE quarterDateStart DATE DEFAULT d+INTERVAL 3 MONTH; | |
RETURN (quarterDateStart - INTERVAL MOD(MONTH(quarterDateStart)-1,3) MONTH - INTERVAL DAY(quarterDateStart)-1 DAY) - INTERVAL 1 DAY; | |
/** Please note that this could be simplified if you use have the qStart function like so: | |
* RETURN qStart(d+INTERVAL 3 MONTH) - INTERVAL 1 DAY | |
*/ | |
END$$ | |
DELIMITER ; | |
SELECT | |
IF(qStart(DATE('2013-01-01')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-01-01'))," != ",DATE('2013-01-01'))) as qs_a, | |
IF(qStart(DATE('2013-02-01')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-02-01'))," != ",DATE('2013-01-01'))) as qs_b, | |
IF(qStart(DATE('2013-03-01')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-03-01'))," != ",DATE('2013-01-01'))) as qs_c, | |
IF(qStart(DATE('2013-04-01')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-04-01'))," != ",DATE('2013-04-01'))) as qs_d, | |
IF(qStart(DATE('2013-05-01')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-05-01'))," != ",DATE('2013-04-01'))) as qs_e, | |
IF(qStart(DATE('2013-06-01')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-06-01'))," != ",DATE('2013-04-01'))) as qs_f, | |
IF(qStart(DATE('2013-07-01')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-07-01'))," != ",DATE('2013-07-01'))) as qs_g, | |
IF(qStart(DATE('2013-08-01')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-08-01'))," != ",DATE('2013-07-01'))) as qs_h, | |
IF(qStart(DATE('2013-09-01')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-09-01'))," != ",DATE('2013-07-01'))) as qs_i, | |
IF(qStart(DATE('2013-10-01')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-10-01'))," != ",DATE('2013-10-01'))) as qs_j, | |
IF(qStart(DATE('2013-11-01')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-11-01'))," != ",DATE('2013-10-01'))) as qs_k, | |
IF(qStart(DATE('2013-12-01')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-12-01'))," != ",DATE('2013-10-01'))) as qs_l, | |
IF(qStart(DATE('2013-01-18')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-01-18'))," != ",DATE('2013-01-01'))) as qs_ab, | |
IF(qStart(DATE('2013-02-18')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-02-18'))," != ",DATE('2013-01-01'))) as qs_bb, | |
IF(qStart(DATE('2013-03-18')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-03-18'))," != ",DATE('2013-01-01'))) as qs_cb, | |
IF(qStart(DATE('2013-04-18')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-04-18'))," != ",DATE('2013-04-01'))) as qs_db, | |
IF(qStart(DATE('2013-05-18')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-05-18'))," != ",DATE('2013-04-01'))) as qs_eb, | |
IF(qStart(DATE('2013-06-18')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-06-18'))," != ",DATE('2013-04-01'))) as qs_fb, | |
IF(qStart(DATE('2013-07-18')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-07-18'))," != ",DATE('2013-07-01'))) as qs_gb, | |
IF(qStart(DATE('2013-08-18')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-08-18'))," != ",DATE('2013-07-01'))) as qs_hb, | |
IF(qStart(DATE('2013-09-18')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-09-18'))," != ",DATE('2013-07-01'))) as qs_ib, | |
IF(qStart(DATE('2013-10-18')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-10-18'))," != ",DATE('2013-10-01'))) as qs_jb, | |
IF(qStart(DATE('2013-11-18')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-11-18'))," != ",DATE('2013-10-01'))) as qs_kb, | |
IF(qStart(DATE('2013-12-18')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-12-18'))," != ",DATE('2013-10-01'))) as qs_lb, | |
IF(qEnd(DATE('2013-01-01')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-01-01'))," != ",DATE('2013-03-31'))) as qe_a, | |
IF(qEnd(DATE('2013-02-01')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-02-01'))," != ",DATE('2013-03-31'))) as qe_b, | |
IF(qEnd(DATE('2013-03-01')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-03-01'))," != ",DATE('2013-03-31'))) as qe_c, | |
IF(qEnd(DATE('2013-04-01')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-04-01'))," != ",DATE('2013-06-30'))) as qe_d, | |
IF(qEnd(DATE('2013-05-01')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-05-01'))," != ",DATE('2013-06-30'))) as qe_e, | |
IF(qEnd(DATE('2013-06-01')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-06-01'))," != ",DATE('2013-06-30'))) as qe_f, | |
IF(qEnd(DATE('2013-07-01')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-07-01'))," != ",DATE('2013-09-30'))) as qe_g, | |
IF(qEnd(DATE('2013-08-01')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-08-01'))," != ",DATE('2013-09-30'))) as qe_h, | |
IF(qEnd(DATE('2013-09-01')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-09-01'))," != ",DATE('2013-09-30'))) as qe_i, | |
IF(qEnd(DATE('2013-10-01')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-10-01'))," != ",DATE('2013-12-31'))) as qe_j, | |
IF(qEnd(DATE('2013-11-01')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-11-01'))," != ",DATE('2013-12-31'))) as qe_k, | |
IF(qEnd(DATE('2013-12-01')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-12-01'))," != ",DATE('2013-12-31'))) as qe_l, | |
IF(qEnd(DATE('2013-01-19')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-01-19'))," != ",DATE('2013-01-01'))) as qe_ab, | |
IF(qEnd(DATE('2013-02-19')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-02-19'))," != ",DATE('2013-03-31'))) as qe_bb, | |
IF(qEnd(DATE('2013-03-19')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-03-19'))," != ",DATE('2013-03-31'))) as qe_cb, | |
IF(qEnd(DATE('2013-04-19')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-04-19'))," != ",DATE('2013-06-30'))) as qe_db, | |
IF(qEnd(DATE('2013-05-19')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-05-19'))," != ",DATE('2013-06-30'))) as qe_eb, | |
IF(qEnd(DATE('2013-06-19')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-06-19'))," != ",DATE('2013-06-30'))) as qe_fb, | |
IF(qEnd(DATE('2013-07-19')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-07-19'))," != ",DATE('2013-09-30'))) as qe_gb, | |
IF(qEnd(DATE('2013-08-19')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-08-19'))," != ",DATE('2013-09-30'))) as qe_hb, | |
IF(qEnd(DATE('2013-09-19')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-09-19'))," != ",DATE('2013-09-30'))) as qe_ib, | |
IF(qEnd(DATE('2013-10-19')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-10-19'))," != ",DATE('2013-12-31'))) as qe_jb, | |
IF(qEnd(DATE('2013-11-19')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-11-19'))," != ",DATE('2013-12-31'))) as qe_kb, | |
IF(qEnd(DATE('2013-12-19')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-12-19'))," != ",DATE('2013-12-31'))) as qe_kl |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment