PostgreSQL – How to Get the Last Day of Month From a Date
To get the last day of the month from a date in PostgreSQL, you can use the DATE_TRUNC function in combination with some date arithmetic. Here’s how you can do it:
SELECT (DATE_TRUNC('MONTH', your_date_column) + INTERVAL '1 MONTH - 1 DAY') AS last_day_of_month FROM your_table;
where your_date_column is the name of your date column and your_table is the name of your table.
DATE_TRUNC('MONTH', your_date_column)truncates the date to the beginning of the month, effectively setting the day to the 1st day of the month.INTERVAL '1 MONTH - 1 DAY'adds an interval of 1 month minus 1 day to the truncated date. This effectively moves to the last day of the current month.
The result will be the last day of the month for each date in the specified column.
For example, if you have a table called sales with a date column named transaction_date, you can retrieve the last day of the month for each transaction like this:
SELECT transaction_date, DATE(DATE_TRUNC('month', transaction_date) + INTERVAL '1 MONTH - 1 DAY') AS last_day_of_month FROM sales;
This will give you a result set with the last day of the month for each transaction date in the sales table.
