How to get the last day of month in postgres?

Tweaking interval expression

PostgreSQL 11 provides a more sleek syntax that combines the intervals in one go:

-- More elegance, less typing! 
SELECT (DATE_TRUNC('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 day')::DATE;


This gives you exactly the same result, yet with a less cryptic and more appealing syntax.

Repeat after me: custom function

When you need to nail down the last day of the month more often than you'd like to admit, why not come up with a custom function?

CREATE OR REPLACE FUNCTION last_day_of_month(date)
RETURNS DATE AS $$
BEGIN
   RETURN (DATE_TRUNC('MONTH', $1) + INTERVAL '1 MONTH' - INTERVAL '1 day')::DATE;
   -- Tada! Here's your last day gift wrapped.
END;
$$ LANGUAGE plpgsql;


Just call it when you wish:

-- Hey, function! What’s the last day this month?
SELECT last_day_of_month(CURRENT_DATE);


Battling against non-date data types

What if your date is hiding behind a numeric(18)? Fear not! You can convert it back to date and then get the last day of that month:

-- Numeric(18)? Ha, date in disguise! Sherlock SQL is on it!
SELECT (DATE_TRUNC('MONTH', TO_DATE(CAST(your_numeric_column AS VARCHAR), 'YYYYMMDD')) + INTERVAL '1 MONTH' - INTERVAL '1 day')::DATE;


Prettifying the output

In case you need the date to dress up prettier in a particular format:

SELECT TO_CHAR((DATE_TRUNC('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH' - INTERVAL '1 day')::DATE, 'YYYY-MM-DD') AS fancy_last_day;
-- Last day in a tux! YYYY-MM-DD is the new black.


Different universe spin: Redshift

For the Amazon Redshift folks, it's like snapping fingers with the LAST_DAY function:

-- Superpower: Snap fingers to get last day! 
SELECT LAST_DAY(TO_DATE(act_date, 'YYYYMMDD')) AS end_of_month FROM your_table;


You might want to check out the Redshift documentation for more on LAST_DAY.

Harnessing SQL Fiddle

Use SQL Fiddle to see the magic happening live. It aids grasping the concept visually and allows you testing various inputs for more insights.

Upgrade is the silver bullet

Stuck on versions older than PostgreSQL 8.0.2? Time for an upgrade! You'll not only be able to use all these neat tricks but also get regular performance improvements and support.

Learning never ends

For further learning, view unconventional resources such as the Date_LastDay wiki or experiment under the hood till you become an SQL maestro.