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

Question

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

Try https://www.postgresql.org/docs/8.2/functions-srf.html and function `generate_series` — Kadet, May 31 '22 at 09:56
@Kadet in version 8.2 generate_series can only used with int... — jian, May 31 '22 at 10:00
@Kadet can i use function generate_series with date? As long as i know argument type in that function is int or big int. That function cant even handle numeric type in my version — nia a, May 31 '22 at 10:01
AFAIK; there is no function like "LastDay" or similar in Postgres, but to get the last day of the month, you can do something like "SELECT (DATE_TRUNC('MONTH', ('202205'||'10')::date) + INTERVAL '1 MONTH - 1 day')::DATE;" which means take the first day of the month, add one month and then substract one day. — Jonas Metzler, May 31 '22 at 10:05
Display last day each month of year .. SELECT (date_trunc('month', '2017-01-05'::date) + interval '1 month' - interval '1 day')::date AS end_of_month; — TechGuy, May 31 '22 at 10:11
@Jonas Metzler yes, but can i generate ALL lastdate or lastday of every month between two dates? — nia a, May 31 '22 at 10:13

score 2 · Answer 1 · answered May 31 '22 at 10:16

2

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

answered May 31 '22 at 10:16

Kadet

1,344
3
10

Jonas Metzler · Answer 2 · 2022-05-31T11:06:03.797

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

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

2 Answers2