Get the last day of the month in SQL

Mastering the calculations

In some cases, you'll have to go beyond the basics. Let's break down these scenarios:

Dealing with leap years

Leap years throw extra party (day) to February. The EOMONTH function handles leap years without breaking a sweat.

-- "Leap of faith": Takes into account the leap years!
SELECT EOMONTH('2024-02-1') AS LastDayOfLeapYearFeb; -- Returns 2024-02-29


Code uncluttering with variable assignment

For readable and reusable code, consider storing the date in a variable:

-- "Pack your bags": Convenient and reusable trip to the last day of the month!
DECLARE @DateVariable DATE = GETDATE();
SELECT EOMONTH(@DateVariable) AS LastDay;


Jumping over the months

EOMONTH allows you to peer into the future or past months. The second parameter is your time machine:

-- "Time-travel": Check out the last day of the next or previous month.
SELECT EOMONTH(GETDATE(), 1) AS LastDayNextMonth; -- Next month's last day
SELECT EOMONTH(GETDATE(), -1) AS LastDayLastMonth; -- Previous month's last day


Visualization

Understanding these functions is a cinch when visualized:

👇 Choose a day, any day (like a magician presenting a deck of cards).

Each day is like a stop on a "month journey". Most months have 30 or 31 days (so that's your usual express train journey 🚇). However, sometimes we have shorter trips in February (28 or 29 days).

And voila, because SQL's EOMONTH function is like a magical train conductor! 🎩🐇

EOMONTH(TargetDate) -- 🚆 This conductor always drops you at the last stop!


End of the line, February 2023! 🎫🚆 Your stop: [28]
Alright, last call for December 2023! 🎫🚆 All aboard: [31]

Crunching the numbers: Performance talk

Performance matters, especially with large datasets. Here's the lowdown:

• EOMONTH is your speed demon 🏎️ - efficient for large datasets.

• The DATEADD and DATEDIFF workaround is like an old reliable pickup 🚛 - more verbose, but gets you there.

Rolling back the SQL Server years

For users stuck in the SQL Server 2008 time-capsule or earlier, DATEADD comes to your rescue:

-- "Old school, baby": SQL Server 2008 and earlier can join the fun, too!
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @DateVariable) + 1, 0)) AS LastDay;


This method avoids any scary string manipulation or casting. Phew!

Juggling different date requirements

The solution is adaptable for diverse date requirements in your databases. Smoothly switch from GETDATE() to other source date forms.