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 postgresql.org/docs/8.2/functions-srf.html and function generate_series — Kadet
– Kadet, Commented May 31, 2022 at 9:56
@Kadet in version 8.2 generate_series can only used with int... — jian
– jian, Commented May 31, 2022 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
– nia a, Commented May 31, 2022 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
– Jonas Metzler, Commented May 31, 2022 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
– TechGuy, Commented May 31, 2022 at 10:11

Kadet · Accepted Answer · 2022-05-31 10:16:07Z

2

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

answered May 31, 2022 at 10:16

Kadet

1,4195 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonas Metzler · Accepted Answer · 2022-05-31 11:06:03Z

1

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

edited May 31, 2022 at 11:06

answered May 31, 2022 at 10:38

Jonas Metzler

6,8363 gold badges9 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related