How to generate last date of every month between two dates in PostgreSQL?-CodePudding

I want to generate a list of last date of every month between two dates. For example, i want to generate

From 2004-01-31 to 2005-08-31 and the result should be like this

2004-01-31
2004-02-29
2004-03-31
2004-04-30
2004-05-31
.
.
.
2005-07-31
2005-08-31

Can anyone help me how to do it and got that result in PostgreSQL? Thanks! Im using postgreSQL 8.2.15

CodePudding user response：

select to_date('2004-01-31','YYYY-MM-DD')   (dates*interval '1 month')
from generate_series(0,19,1) dates

CodePudding user response：

You can calculate how many months are between start date and end date and then build a list of start date 1 month as long as the end date is not reached:

SELECT TO_DATE('2004-01-31','YYYY-MM-DD')   (d*INTERVAL '1 MONTH')
FROM generate_series(0,(SELECT CAST(EXTRACT(YEAR FROM age
(TO_DATE('2005-08-31','YYYY-MM-DD'), TO_DATE('2004-01-31','YYYY-MM-DD'))) * 12  
EXTRACT(MONTH FROM age(TO_DATE('2005-08-31','YYYY-MM-DD'), 
TO_DATE('2004-01-31','YYYY-MM-DD'))) AS INT)),1) d