Trying to create 6 Months Rolling for current Month in MYSQL

Question

I am trying to achieve the following layout. If I select curdate() which is june i should get previous 6 months.6 months should include June also .i.e. if curdate = june 25-2018 my out out should be months from June-2018 until Jan-2018. Can anyone please help me out with syntax. All I have now is individual queries which will give me previous 6 months in MYSQL.

Month Previous Months june may apr mar feb jan may apr mar feb jan DEc

This is desired output. Current Month Previous Month june May,Apr,march,feb,jan may apr, mar, feb , jan , dec — user7571656

SeanW333 SeanW333 · Accepted Answer · 2018-06-26T00:24:58

Use the Date_Add function. See examples below:

/* Query to select last 6 months (to the day) */
SELECT
    *
FROM 
    test_dates
WHERE 
    `date` > date_add(curdate(), INTERVAL -6 MONTH)



/* Query to get every date from 01/01/2018 to 06/25/2018 */
SELECT
    `date`
FROM
    test_dates
WHERE
    STR_TO_DATE(CONCAT(YEAR(`date`), '-', MONTH(`date`), '-01'), '%Y-%m-%d') 
    >= 
    STR_TO_DATE(
                CONCAT(
                        YEAR(DATE_ADD(curDate(), INTERVAL -5 MONTH)),
                        '-',
                        MONTH(DATE_ADD(curDate(), INTERVAL -5 MONTH))
                        , '-01'
                        )
                , '%Y-%m-%d'
                );

Code I used to setup test:

/* Create a table to test */
DROP TABLE IF EXISTS test_dates;
CREATE TABLE IF NOT EXISTS test_dates (
    `date` datetime
);


/* Insert Test Dates */
INSERT INTO test_dates (`date`) VALUES ('2018-06-01');
INSERT INTO test_dates (`date`) VALUES ('2018-05-01');
INSERT INTO test_dates (`date`) VALUES ('2018-04-01');
INSERT INTO test_dates (`date`) VALUES ('2018-03-01');
INSERT INTO test_dates (`date`) VALUES ('2018-02-01');
INSERT INTO test_dates (`date`) VALUES ('2018-01-01');
INSERT INTO test_dates (`date`) VALUES ('2017-012-01');
INSERT INTO test_dates (`date`) VALUES ('2017-011-01');

Trying to create 6 Months Rolling for current Month in MYSQL

1 Answers