Add LastDayOfMonth database function

[nobilebeniamino]

Code of Conduct

• I agree to follow Django's Code of Conduct

Feature Description

Add a built-in LastDayOfMonth database function that returns the last calendar day of the month for a given DateField or DateTimeField expression.

Problem

Today developers must hand-roll backend-specific Func subclasses (or wrap raw SQL) to calculate “last day of month”. That works but it’s verbose, copied across code-bases and easy to get subtly wrong (leap years, Oracle quirks, new DB versions). A small built-in helper removes that friction and raises the quality bar.

Why a core helper is better than ad-hoc Func / FuncWrapper:

• One-liner, zero boiler-plate – LastDayOfMonth("start").
• Cross-backend guarantee – ORM ships the right SQL for PostgreSQL, MySQL/MariaDB, SQLite and Oracle, covered by the test-suite.
• Official docs & IDE discoverability – lives next to TruncMonth, ExtractDay, etc.
• Clearer code reviews – intent obvious without opening a helper file; no naming drift (MonthEnd, EndOfMonth, …).
• Safer upgrades – Django can warn if backend syntax changes, while custom SQL might silently break.
• Negligible maintenance cost – MySQL/MariaDB and Oracle expose native LAST_DAY(), and the Postgres/SQLite expression is a single date-math line.

Request or proposal

proposal

Additional Details

Use-cases

• Payroll or invoice cut-off dates
• Month-end KPI / reporting roll-ups
• Any query that needs “<= last day of the month”

Proposed API

from django.db.models.functions import LastDayOfMonth
qs.annotate(period_end=LastDayOfMonth("issued_at"))

Backend SQL mapping (illustrative)

Backend	SQL emitted
PostgreSQL	date_trunc('month', exp + interval '1 month') - interval '1 day'
MySQL/MariaDB	LAST_DAY(exp)
SQLite	date(exp, '+1 month', 'start of month', '-1 day')
Oracle	LAST_DAY(exp) (pure-date via TRUNC/NUMTODSINTERVAL)

Implementation Suggestions

• class LastDayOfMonth(Func) in django/db/models/functions/datetime.py
  • arity = 1
  • output_field = models.DateField()
  • backend methods: as_postgresql(), as_mysql(), as_sqlite(), as_oracle()
• Tests: tests/db_functions/datetime/test_last_day_of_month.py using a simple model with a DateField.
• Docs: add to “Database Functions” reference and to the upcoming 5.2 release notes under “Minor features”.
• I’m happy to open the PR and iterate on review feedback.

[github-actions]

Thank you nobilebeniamino for sharing your idea! We have a lot of them so please be patient. You can see the current queue here.

Community instructions

For commenters, please use the emoji reactions on the issue to express support, and/or concern easily. Please use the comments to ask questions or contribute knowledge about the idea. It is unhelpful to post comments of "I'd love this" or "What's the state of this?"

Reaction Guide

• 👍 This is something I would use
• 👎 This is something that would cause problems for me or Django
• 😕 I’m indifferent to this
• 🎉 This is an easy win

[tim-schilling]

I'm a bit confused. The justification that this is low maintenance feels contradictory to the justification to include it of "it’s verbose, copied across code-bases and easy to get subtly wrong (leap years, Oracle quirks, new DB versions)."

If I'm understanding this correctly, there are DB specific functions or expressions that you'd like to expose through the ORM. They aren't particularly gnarly or difficult, but it's annoying to copy and paste those classes?

[LilyAcorn]

I think this might be worth including in core, if there's enough demand for it. But I suspect this is actually pretty niche, and is probably better suited to a third-party package.

The main pro of having it in core would be discoverability. The main pro of having it in a third party library is that it's immediately available without having to wait for a new Django release and having to upgrade to that version.

[nobilebeniamino]

I'm a bit confused. The justification that this is low maintenance feels contradictory to the justification to include it of "it’s verbose, copied across code-bases and easy to get subtly wrong (leap years, Oracle quirks, new DB versions)."

If I'm understanding this correctly, there are DB specific functions or expressions that you'd like to expose through the ORM. They aren't particularly gnarly or difficult, but it's annoying to copy and paste those classes?

Hi Tim, thanks for taking the time to look at this!

Sorry if my explanation wasn’t clear — this is my first public feature proposal and English isn’t my first language, so I may have chosen my words poorly.

• Low-maintenance: the SQL itself is simple (and even native on MySQL/MariaDB and Oracle), so it shouldn’t add much ongoing burden to Django’s code-base.

• High friction for users: despite being simple, people still end up copy-pasting a custom Func class or raw SQL into every project, which scatters duplicate code and unit tests around the ecosystem.
  In my case I needed it while calculating a set of KPIs across multiple databases; having it built-in would have kept the query code much cleaner and identical on all back-ends.

So, the core value isn’t that the logic is complex, but that it removes boiler-plate and makes a cross-backend helper discoverable in one place.
Of course I understand it’s a niche need; I’m happy for the discussion to decide whether that benefit is worth adding to core or better served by a third-party package.

If anything is still unclear, let me know and I’ll try to elaborate.

Thanks again for the feedback!

[nanuxbe]

Hello all,

as with most request I would recommend you start with a 3rd-party app as that would be a first win and can also be used as proof-of-concept (required further in this process).

For the kind of application that deal with things aching to company management and the like I know that this can sometimes be useful but...
On the other hand, I have been able to substitute most "last day of the month" need by using Django's ORM built-in filter on the month itself (with the most obvious usecase being date between the first and last of a specified month). Do you have any specific example you'd like to share where this is something you'd want to use? <- answer to this question would help determine the breadth of possible use.

[adamchainz]

I feel the pain, but I do worry about the scope creep here... If we provide LastDayOfMonth, why not FirstDayOfMonth, Nth weekday of the month (like 2nd Tuesday), LastDayOfQuarter, LastDayOfWeek, etc. ? The same arguments could be made for all of them.

[shangxiao]

I echo Adam's sentiment as I find it to be a little niche, additionally it's not hard to define a function for it when the need arises:

class LastDayOfMonth(Func):
    template = "(date_trunc('month', %(expressions)s) + interval '1 month - 1 day')::date"
    output_field = DateField()

I personally maintain a whole host of snippets that I find useful that I copy & paste into projects but I don't think would be a good idea in Django. These are more useful as gists or snippet repos 👍 Also maybe think about suggesting it with a project like django-extensions 🤔

[nanorepublica]

Building on David's comment about snippets, I wonder if we can promote/improve Django Snippets more in the documentation?

My general thought would be linking to relevant snippets in the appropriate place in the documentation with the caveat that they aren't going to be checked that they work as often and should be viewed as a starting point or common examples to be used. It's creating a clear expectation that these snippets are expected to be copied into your project and will not get migrated to core

[nobilebeniamino]

Hi everyone, thanks for all the feedbacks
I’ll try to answer and to address each point

[nobilebeniamino]

Hello all,

as with most request I would recommend you start with a 3rd-party app as that would be a first win and can also be used as proof-of-concept (required further in this process).

For the kind of application that deal with things aching to company management and the like I know that this can sometimes be useful but... On the other hand, I have been able to substitute most "last day of the month" need by using Django's ORM built-in filter on the month itself (with the most obvious usecase being date between the first and last of a specified month). Do you have any specific example you'd like to share where this is something you'd want to use? <- answer to this question would help determine the breadth of possible use.

Hi @nanuxbe,
here's an example

On a recent KPI project we had to feed several tables (from a Postegres and a Oracle db) into a BI tool that already uses a date dimension keyed by the actual month-end (2025-05-31, not just 2025-05).

Having that exact date:

• lets us join facts from different databases on the same key;
• is required for interest-accrual and aging calculations that need the precise cut-off day;
  (also it avoids us extra Python post-processing when exporting or charting)

How we built it (built-ins only)

from datetime import timedelta
from django.db.models import DateField, ExpressionWrapper, Func, Value
from django.db.models.functions import TruncMonth

month_start = TruncMonth("value_date")                       # 2025-05-01
month_plus1 = Func(month_start, Value("1 month"), function="+")
month_end = ExpressionWrapper(
    month_plus1 - Value(timedelta(days=1)),
    output_field=DateField(),
)

balances = (
    Balance.objects
    .annotate(month_end=month_end)
    .values("account_id", "month_end")
    .annotate(closing_balance=Sum("amount"))
)

Four steps, and it only runs on Postgres; on Oracle we still copy a custom Func with template="LAST_DAY(%(expressions)s)".

How the same query would be with the helper python

from django.db.models.functions import LastDayOfMonth

balances = (
    Balance.objects
    .annotate(month_end=LastDayOfMonth("value_date"))
    .values("account_id", "month_end")
    .annotate(closing_balance=Sum("amount"))
)

One line, identical SQL on both back-ends

[nobilebeniamino]

I feel the pain, but I do worry about the scope creep here... If we provide LastDayOfMonth, why not FirstDayOfMonth, Nth weekday of the month (like 2nd Tuesday), LastDayOfQuarter, LastDayOfWeek, etc. ? The same arguments could be made for all of them.

Hi @adamchainz and @shangxiao

• First day is already covered – TruncMonth() returns the first day of the month, so we’d just be adding its natural counterpart.
• Other patterns are different beasts – “2nd Tuesday”, “last day of quarter” need extra parameters (weekday, quarter index, calendars), so they’d merit separate proposals when real demand appears.

But my main point is: Vendors already ship LAST_DAY() – Oracle, MySQL and MariaDB expose it natively. I’m far from a DB guru, so when three major engines decide it’s worth a built-in, I take that as evidence the use-case is common enough to justify a one-liner in Django too.

So the idea is to round out TruncMonth() with its “month-end” twin, not to open the flood-gates to every calendar helper imaginable.

Happy to reconsider if the consensus is still that it belongs in a third-party package, but that’s the rationale I’m working from.