Get First and Last Day of Month in SQL Server for Reports

by

When building reports in SQL Server, it’s common to filter or group data by month. A frequent requirement is to get the first and last day of a given month, often so you can build ranges for your WHERE clauses or generate summary tables. SQL Server gives us several ways to do this, and once you know the patterns, it’s quick to adapt for different reporting needs.

Getting the Last Day of the Month

For getting the last day of the month, the EOMONTH() function is perfect. In fact it was built specifically for returning the last day of the month:

DECLARE @Date DATE = '2025-08-23';

SELECT EOMONTH(@Date)

Result:

2025-08-31

The nice thing about EOMONTH() is that it automatically handles leap years and different month lengths, so you don’t need extra logic.

Before SQL Server 2012, we would often need to use tricks like this:

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0));

It looks messy when compared to EOMONTH(), but the idea is:

  • DATEDIFF(MONTH, 0, @Date) counts months from the zero date (1900-01-01).
  • Adding 1 to that and wrapping with DATEADD(MONTH, …, 0) jumps to the first day of the next month.
  • Subtracting 1 day lands you on the last day of the current month.

This still works in modern SQL Server, but EOMONTH() is cleaner and easier to read.

Getting the First Day of the Month

Option 1: The first day of a month is easy to calculate. You can take any date and use the EOMONTH() function in combination with DATEADD() to roll it back to day one.

DECLARE @Date DATE = '2025-08-23';

SELECT DATEADD(DAY, 1, EOMONTH(@Date, -1));

Result:

2025-08-01

Here’s how it works:

  1. EOMONTH(@Date, -1) returns the last day of the previous month.
  2. Adding 1 day gives you the first day of the current month.

Option 2: If you’re using SQL Server 2022 or later, you can use the DATETRUNC() function to truncate the date to the first day of the month:

DECLARE @Date DATE = '2025-08-23';

SELECT DATETRUNC(MONTH, @Date);

Result:

2025-08-01

When we provide MONTH as the first argument, DATETRUNC() truncates the value to the first day of the month.

Option 3: There’s also a another approach using DATEFROMPARTS(), but it’s less flexible if you’re working with dynamic ranges:

DECLARE @Date DATE = '2025-08-23';

SELECT DATEFROMPARTS(YEAR(@Date), MONTH(@Date), 1);

Using in Reports

Suppose you want to generate a monthly sales report. A common pattern is filtering by a month’s range:

DECLARE @ReportMonth DATE = '2025-07-15';

SELECT *
FROM Sales
WHERE SaleDate >= DATEADD(DAY, 1, EOMONTH(@ReportMonth, -1))
  AND SaleDate <= EOMONTH(@ReportMonth);

This will include everything from 2025-07-01 through 2025-07-31. Notice how we didn’t rely on the day in @ReportMonth. This is because any date in the month works, and the logic handles the rest.

If you prefer half-open ranges (which can be cleaner in some reporting scenarios), you can write it like this:

WHERE SaleDate >= DATEADD(DAY, 1, EOMONTH(@ReportMonth, -1))
  AND SaleDate < DATEADD(DAY, 1, EOMONTH(@ReportMonth));

That way you don’t have to worry about SaleDate having a time component beyond midnight.