django / django/new-features

Add LastDayOfMonth database function

Open
#38 25 comments 56 reactions 0 assignees View on GitHub
Documentation Models/ORM Needs team Third Party Package
Dominant language
No language data
Stars
188
Forks
7
PR merge metrics
No merged PRs in 30d

Description

### Code of Conduct

- [x] 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.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.