How to get date increments in between start date and end date in MYSQL

Question

I have sample dates in a table and what I need to get is each of the months between the start date and end date.

sample :

 ID   Startdate    EndDate
 1   01-01-2019  01-03-2019
 2   01-08-2019  01-02-2020

I need to fetch months and year from these dates.

Desired output :

ID  Dates
1  January 2019
1  February 2019
1  March 2019
2  August 2019
2  September 2019
2  October 2019
2  November 2019
2  December 2019
2  January 2020
2  February 2020

How cah I achieve this in MySQL and how to do increment or any loop kind of operation. On the query side I'm not getting any idea to move on this.

Check this thread on generating rows. --> https://stackoverflow.com/questions/701444/how-do-i-make-a-row-generator-in-mysql — Digvijay S, Mar 18 '20 at 03:50
Are those dates or varchars? They're not in MySQL date format... — Nick, Mar 18 '20 at 04:54
Consider handling issues of data display in application code — Strawberry, Mar 18 '20 at 07:02
@Nick..if i want the same result in 2020-01-01,2020-02-01 etc what i need to modify in the query — mohan111, Apr 08 '20 at 03:00

score 1 · Accepted Answer · answered Mar 18 '20 at 05:55

Here are a couple of ways to achieve this. The first will only work on MySQL 8+ and uses a recursive CTE to generate the months between StartDate and EndDate:

WITH RECURSIVE CTE AS (
  SELECT ID, Startdate AS d, EndDate
  FROM dates
  UNION ALL
  SELECT ID, d + INTERVAL 1 MONTH, EndDate
  FROM CTE
  WHERE d < EndDate
)
SELECT ID, DATE_FORMAT(d, '%M %Y') AS Dates
FROM CTE
ORDER BY ID, d

The second (which will run on any version of MySQL) uses a numbers table (in this case numbers from 0 to 99, allowing for a range of up to 99 months between StartDate and EndDate; if you need longer, adding more tables to the CROSS JOIN will increase that range by a factor of 10 for each table added) to generate the list of months difference, this is then JOINed to the original table so that the generated date Startdate + INTERVAL n.n MONTH is less than or equal to EndDate:

SELECT ID, DATE_FORMAT(Startdate + INTERVAL n.n MONTH, '%M %Y') AS Dates
FROM dates
JOIN (
  SELECT n10.n * 10 + n1.n * 1 AS n
  FROM (
    SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) n10
  CROSS JOIN (
    SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) n1
) n ON Startdate + INTERVAL n.n MONTH <= EndDate
ORDER BY ID, Startdate + INTERVAL n.n MONTH

Having generated our list of dates, we format it using DATE_FORMAT and a format string of %M %Y. For both queries the output is:

ID  Dates
1   January 2019
1   February 2019
1   March 2019
2   August 2019
2   September 2019
2   October 2019
2   November 2019
2   December 2019
2   January 2020
2   February 2020

Demo on dbfiddle

if i want the same result in 2020-01-01,2020-02-01 etc what i need to modify in the query @Nick — mohan111, Apr 08 '20 at 02:50
@mohan111 change `'%M %Y'` in the `DATE_FORMAT` to `'%Y-%m-%d'` — Nick, Apr 08 '20 at 03:07
yes changed to the above mentioned format but it giving like 2019-January-1st,2019-February-1st etc;. I'm looking for yyyy-mm-dd (i.e 2019-01-01) — mohan111, Apr 08 '20 at 03:10
@mohan111 works fine for me: https://www.db-fiddle.com/f/5tMDp56G79ePNhAgZZ5vxk/1 — Nick, Apr 08 '20 at 03:12
yes @nick i have added capital letters %Y %M%D i have changed it ...thanks — mohan111, Apr 08 '20 at 03:18
@mohan111 Once again it looks like you have a good answer already... — Nick, Apr 28 '20 at 23:48
can you please have a look : https://stackoverflow.com/questions/61530970/how-to-fetch-records-based-on-the-dates-in-mysql?noredirect=1#comment108843802_61530970 — mohan111, May 01 '20 at 09:20

How to get date increments in between start date and end date in MYSQL

1 Answers1