Get the Last Day of the Month in DuckDB with LAST_DAY()

DuckDB provides us with a good selection of functions for working with date and timestamp values. One such function is the LAST_DAY() function, which returns the last day of the month of the given date.

The way it works is, we pass a date to the function, and it returns the date of the last day of that month.

Syntax

We can use the function in the following ways:

last_day(DATE) -> DATE
last_day(TIMESTAMP) -> DATE
last_day(TIMESTAMP WITH TIME ZONE) -> DATE

So it returns a DATE value, regardless of whether we passed a DATE or a TIMESTAMP/TIMESTAMPZ value.

Example

Here’s a simple example:

SELECT last_day( DATE '2025-02-01' ) AS last_day_of_month;

Result:

+-------------------+
| last_day_of_month |
+-------------------+
| 2025-02-28        |
+-------------------+

In this case the date we passed is in February, and so last_day() returned the date of the last day of that month. February has 28 days (unless it’s a leap year), and so the resulting date reflects this.

Get the Last Day of the Current Month

We can use last_day() in conjunction with a function like current_date to get the last day of the current month at the time we run the query:

SELECT 
    current_date,
    last_day( current_date ) AS last_day_of_this_month;

Result:

+--------------+------------------------+
| current_date | last_day_of_this_month |
+--------------+------------------------+
| 2025-03-08   | 2025-03-31             |
+--------------+------------------------+

Get the Last Day of Next Month

We can use a function like date_add() to add a month to the current date, and then get the last day of that month:

SELECT 
    current_date,
    last_day( date_add( current_date, interval 1 month ) ) AS last_day_of_next_month;

Result:

+--------------+------------------------+
| current_date | last_day_of_next_month |
+--------------+------------------------+
| 2025-03-08   | 2025-04-30             |
+--------------+------------------------+

Of course, it doesn’t have to be one month. We can add as many months as we like, or other date units such as years.

Add 3 Months to an Annual Subscription

Suppose we have a special deal where we add three months to a user’s subscription if they sign up for a year. But we don’t just add three months, but we extend it to the end of the three months. We could therefore do something like this:

SELECT 
    current_date,
    date_add( current_date, interval 1 year ) AS original_subscription_end,
    last_day( date_add( current_date, interval '1 year 3 months' ) ) AS extended_subscription_end;

Result:

+--------------+---------------------------+---------------------------+
| current_date | original_subscription_end | extended_subscription_end |
+--------------+---------------------------+---------------------------+
| 2025-03-08   | 2026-03-08 00:00:00       | 2026-06-30                |
+--------------+---------------------------+---------------------------+

Passing the Wrong Argument Type

Passing the wrong argument type results in an error:

SELECT last_day( 73 );

Result:

Binder Error: No function matches the given name and argument types 'last_day(INTEGER_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	last_day(DATE) -> DATE
	last_day(TIMESTAMP) -> DATE
	last_day(TIMESTAMP WITH TIME ZONE) -> DATE

LINE 1: SELECT last_day( 73 );
               ^

Fortunately, DuckDB shows us which data types are accepted with the function. We can either explicitly cast the value to one of the supported types, or use the correct type to start with.