We can use the following technique in PostgreSQL to return the last day of a given month.

This could be the last day of the current month, or the last day of the month based on a date that we specify.

End of the Current Month

Here’s an example that returns the last day of the current month:

SELECT (date_trunc('month', now()) + interval '1 month - 1 day');

Result:

2022-04-30 00:00:00+10

This uses PostgreSQL’s date_trunc() function, along with some date arithmetic to return the results we want. This function truncates a date/time value to a specified precision.

In this case I use the now() function to return the current date, and the 'month' argument modifies that date to the beginning of the month. I then added one month to that (which brings it to the start of the following month), then subtracted one day from that date to give us the end of the previous month (which is the end of the current month).

We can cast it to a date value if required:

SELECT (date_trunc('month', now()) + interval '1 month - 1 day')::date;

Result:

2022-04-30

Here it is again, along with the actual date that I ran the example:

SELECT 
    now()::date AS "Current Date",
    (date_trunc('month', now()) + interval '1 month - 1 day')::date AS "End of Month";

Result:

+--------------+--------------+
| Current Date | End of Month |
+--------------+--------------+
| 2022-04-09   | 2022-04-30   |
+--------------+--------------+

End of a Specified Month

It doesn’t have to be the end of the current month. We can specify any date, and it will return the end of the month, based on that date.

Example:

SELECT (date_trunc('month', date '2030-07-14') + interval '1 month - 1 day')::date;

Result:

2030-07-31

Database Example

Here’s an example that uses dates from a database:

SELECT
    rental_date,
    (date_trunc('month', rental_date) + interval '1 month - 1 day')::date AS "End of Month"
FROM rental WHERE customer_id = 459 LIMIT 10;

Result:

+---------------------+--------------+
|     rental_date     | End of Month |
+---------------------+--------------+
| 2005-05-24 22:54:33 | 2005-05-31   |
| 2005-06-17 02:50:51 | 2005-06-30   |
| 2005-06-17 09:38:22 | 2005-06-30   |
| 2005-06-17 16:40:33 | 2005-06-30   |
| 2005-06-20 02:39:21 | 2005-06-30   |
| 2005-06-20 12:35:44 | 2005-06-30   |
| 2005-06-20 12:42:00 | 2005-06-30   |
| 2005-06-21 02:39:44 | 2005-06-30   |
| 2005-07-06 00:22:29 | 2005-07-31   |
| 2005-07-08 02:51:23 | 2005-07-31   |
+---------------------+--------------+