Date Boundaries With DATETRUNC and EOMONTH in SQL Server 2022

The first day of a month used to appear as a hard-to-read DATEADD and DATEDIFF expression in many SQL Server reports. DATETRUNC makes the start explicit in SQL Server 2022, while EOMONTH finds month ends and offsets. The most important use is a clear half-open WHERE range that keeps the date column searchable.

Compare DATEADD Math With DATETRUNC

The old pattern counts whole months from an anchor and adds them back. It works, but the intent is hidden. DATETRUNC(month, value) says the same thing directly for SQL Server 2022 and later. EOMONTH returns a date for the last day of the month. Test a date with a time component to see the boundary clearly.

DECLARE @d datetime2(3) = '2026-09-24T15:37:12.345';
SELECT DATEADD(month, DATEDIFF(month, 0, @d), 0)
         AS old_month_start,
       DATETRUNC(month, @d) AS month_start,
       EOMONTH(@d) AS month_last_date;

The old expression returns datetime here, whatever the input. DATETRUNC returns the input's type and scale, which is datetime2(3) in this example. EOMONTH returns date, so it is a calendar boundary, not the last possible timestamp of the day. I keep those type differences visible when comparing results in an application.

Find Quarter and Week Starts With DATETRUNC

It supports quarter, month, week, and iso_week among other date parts. The week form follows the session's @@DATEFIRST setting; iso_week starts on Monday regardless of that setting. A reporting contract must say which week definition it uses. Quarter starts are not affected by DATEFIRST.

DECLARE @d datetime2(0) = '2026-09-24T15:37:12';
SELECT DATETRUNC(quarter,@d) AS quarter_start,
       DATETRUNC(month,@d) AS month_start,
       DATETRUNC(week,@d) AS session_week_start,
       DATETRUNC(iso_week,@d) AS monday_week_start,
       @@DATEFIRST AS datefirst_value;

If a customer report uses a fiscal quarter, calendar quarter truncation is not enough. Use a fiscal calendar table or an explicitly documented offset. The function improves expression clarity; it does not decide business calendars for you.

Filter With a Half-Open Range

To select September rows from a datetime column, use OrderDate >= @start AND OrderDate < @next_start. Do not use EOMONTH as an inclusive upper bound for a datetime value. It converts to midnight at the start of the last day, so later times that day are missed. Do not apply DATETRUNC to every table row in the WHERE predicate if you want a simple index range seek.

DECLARE @anchor date = '2026-09-24';
DECLARE @start date = DATETRUNC(month,@anchor);
DECLARE @next_start date = DATEADD(month,1,@start);
SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @start
  AND OrderDate < @next_start;

An index beginning with OrderDate can support a seek on that range when the rest of the query and types allow it. Compare the actual plan and logical reads with a predicate such as DATETRUNC(month, OrderDate) = @start. The half-open form also handles all fractional seconds without guessing the last representable time.

Use EOMONTH Offsets for Neighboring Months

EOMONTH accepts an optional month offset. EOMONTH(@d,-1) is the final date of the previous month; EOMONTH(@d,1) is the final date of the next month. Add one day to an end-of-month result to get the next month's first date. For the previous month's first date, use an offset of -2 and add one day.

DECLARE @d date = '2026-09-24';
SELECT DATEADD(day,1,EOMONTH(@d,-2)) AS previous_start,
       EOMONTH(@d,-1) AS previous_end_date,
       DATETRUNC(month,@d) AS current_start,
       EOMONTH(@d) AS current_end_date,
       DATEADD(day,1,EOMONTH(@d)) AS next_start,
       EOMONTH(@d,1) AS next_end_date;

For range predicates, use the first date of the following period as the exclusive end. EOMONTH end dates are useful for display and accounting labels. Separate those two jobs in the code so a report does not drop the final day's afternoon transactions.

Test Boundaries at Year Ends

December to January and leap-year February are good test cases. Both functions handle calendar transitions, but surrounding code can still use an incorrect offset or inclusive comparison. Test dates at 00:00:00, the final fractional second before the next month, and exactly midnight on the next month's first day.

I also check parameter types. A string converted under a language-dependent format can shift the intended date before any boundary function runs. Use typed parameters and unambiguous literals. Store timestamps in a consistent time zone and convert the business reporting zone before finding local month boundaries if the report is defined by local dates.

Read the Plan, Not Only the Result

Two predicates can return the same rows while using different access paths. A function on the column can prevent a straightforward seek and force a scan or residual computation. The half-open range keeps the column bare and gives the optimizer clear lower and upper bounds. Check actual reads on representative data; a small table can be scanned even when a seek is possible.

I keep one helper expression for the start and next start in the report procedure, then reuse those parameters across the count and detail queries. That avoids slightly different boundary logic in two sections of the same dashboard. The new functions make the intent readable, while the range shape keeps it practical.

Choose Types at the Boundary

A date-only variable compared to a datetime2 column is usually clear, but implicit conversion direction and parameter type can affect the plan. Declare the start and next-start values in types compatible with the column, and inspect the actual plan for a conversion on the indexed column. With datetimeoffset, decide the reporting time zone before calculating local month boundaries. A UTC month and a local business month can include different rows around midnight.

Use the Same Boundary for Totals and Detail

A report can show a monthly count from one query and detail rows from another. If one uses <= EOMONTH(@d) and the other uses < @next_start, they can disagree on the last day. Calculate the two boundaries once and pass them to both queries. Include a regression row at the last day's late hour and another exactly at next month's midnight.

I make the test span February in a leap year and December into January. A date function usually handles those transitions correctly; the surrounding DATEADD offset is where mistakes appear. The half-open pattern stays the same for quarters and weeks: inclusive period start, exclusive start of the next period.

Check DATETRUNC Compatibility Before Deployment

The function requires SQL Server 2022 or later. A database moved back to an older engine cannot compile a module that calls it. EOMONTH has broader version support, but version checks still belong in a deployment. Keep the older DATEADD and DATEDIFF expression in a compatibility branch only where an older supported instance truly needs it. The goal is readable code without surprising a mixed-version estate.

Which timestamp belongs to the next period rather than the one you are closing?

Related reading on this blog: A Walkthrough: DATETRUNC Function in SQL Server and Simple Method to Find FIRST and LAST Day of Current Date.

A month-end filter is not an end-of-day guess, it is a half-open timestamp range.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.