Rolling Date Ranges Forward Without Hard-Coded Years

January arrives, and yesterday's report still filters for the previous year. Removing hard-coded years lets date boundaries move with the reporting clock while keeping the filter usable by an index.

Choose a Clock Before Dropping Hard-Coded Years

A rolling report starts with a definition of now. GETDATE returns the server's current local date and time. That clock must match the meaning of the timestamp column being filtered.

I check the timestamp convention before reviewing the WHERE clause. A UTC column needs UTC boundaries or an explicit local-to-UTC conversion. Server midnight doesn't represent every customer's midnight.

Capture the reporting moment once in a variable. Repeated clock calls can cross a boundary during execution. A stored value also gives you an anchor for explaining the report later.

The following examples use a server-local reporting clock and matching stored timestamps. Run them together in a test connection. For repeatable tests, replace the captured clock with a fixed datetime2 value.

CREATE TABLE #RangeEvents
(
    EventId int NOT NULL PRIMARY KEY,
    RecordedAt datetime2(7) NOT NULL
);
CREATE INDEX IX_RangeEvents_RecordedAt
ON #RangeEvents (RecordedAt);
INSERT #RangeEvents VALUES
(1, '2024-02-29T12:00:00'),
(2, '2024-12-31T23:59:59.9999999'),
(3, '2025-01-01T00:00:00'),
(4, '2025-07-15T09:30:00');
DECLARE @AsOf datetime2(7) = CONVERT(datetime2(7), GETDATE());
DECLARE @YearStart date = DATEFROMPARTS(YEAR(@AsOf), 1, 1);
SELECT @AsOf AS ReportingMoment, @YearStart AS CurrentYearStart;

The dates in the fixture are synthetic boundary examples. They don't describe an observed workload. An empty result against today's clock remains valid when the fixture contains only earlier dates.

A report runner can accept the reporting moment as an input instead. That makes backdated reports reproducible. Record the supplied clock alongside the result rather than changing the procedure's SQL each time.

Replace Hard-Coded Years With Bare-Column Filters

Build the start and end values outside the predicate. Compare the stored timestamp directly with those values. Functions wrapped around the column make an ordinary range lookup harder to use.

A half-open range includes its start and excludes its end. Adjacent periods then meet at one boundary without sharing a row. No invented final millisecond is required.

DECLARE @AsOf datetime2(7) = CONVERT(datetime2(7), GETDATE());
DECLARE @YearStart date = DATEFROMPARTS(YEAR(@AsOf), 1, 1);
DECLARE @NextYearStart date = DATEADD(year, 1, @YearStart);
DECLARE @LastYearStart date = DATEADD(year, -1, @YearStart);
SELECT EventId, RecordedAt
FROM #RangeEvents
WHERE RecordedAt >= @YearStart AND RecordedAt < @NextYearStart
ORDER BY RecordedAt, EventId;

SELECT EventId, RecordedAt
FROM #RangeEvents
WHERE RecordedAt >= @LastYearStart AND RecordedAt < @YearStart
ORDER BY RecordedAt, EventId;

These queries represent complete calendar years. The current-year query includes future-dated rows within that year. Decide whether those rows belong in the report before calling it a year-to-date result.

Replacing hard-coded years doesn't guarantee an index seek. Cardinality, available indexes, and the rest of the query still matter. Inspect the execution plan and reads on the actual workload.

Avoid BETWEEN for timestamp ranges ending at a date boundary. Its upper limit is inclusive. A midnight upper bound either omits the rest of that date or overlaps the next period.

Keep parameter types compatible with the indexed column. An implicit conversion applied to that column can undermine the intended range access. Check the plan for conversion warnings during testing.

Select the Last Twelve Completed Months

A completed-month report ends at the start of the current month. Move backward twelve months from that boundary. This excludes the current partial month regardless of today's day number.

Rolling twelve months through now answers a different question. Its first and last months are partial. Don't use that label for a report built from twelve completed calendar months.

DECLARE @AsOf datetime2(7) = CONVERT(datetime2(7), GETDATE());
DECLARE @MonthEnd date = DATEFROMPARTS(YEAR(@AsOf), MONTH(@AsOf), 1);
DECLARE @MonthStart date = DATEADD(month, -12, @MonthEnd);
SELECT @MonthStart AS IncludedStart, @MonthEnd AS ExcludedEnd;
SELECT EventId, RecordedAt
FROM #RangeEvents
WHERE RecordedAt >= @MonthStart AND RecordedAt < @MonthEnd
ORDER BY RecordedAt, EventId;

Month-start anchors avoid the short-month adjustment that affects dates near a month's end. Every selected boundary is the first day. February therefore needs no separate branch in this calculation.

Which months must be complete in your report? That answer settles the end boundary. A chart title can't repair an incorrectly chosen reporting interval.

I write the excluded end next to the included start during review. That small habit exposes mismatched period definitions. A date literal can retire without a farewell speech.

Compare Equivalent Year-to-Date Cutoffs

Year to date begins at the current year's first day. Its upper boundary is the captured reporting moment. The earlier comparison uses that moment shifted backward one calendar year.

That choice compares calendar positions, including the time of day. It doesn't align weekdays or elapsed seconds. Business reporting must choose which meaning is appropriate.

DECLARE @AsOf datetime2(7) = CONVERT(datetime2(7), GETDATE());
DECLARE @ThisStart date = DATEFROMPARTS(YEAR(@AsOf), 1, 1);
DECLARE @PriorStart date = DATEADD(year, -1, @ThisStart);
DECLARE @PriorAsOf datetime2(7) = DATEADD(year, -1, @AsOf);
SELECT N'Current YTD' AS PeriodName, COUNT_BIG(*) AS EventCount
FROM #RangeEvents
WHERE RecordedAt >= @ThisStart AND RecordedAt < @AsOf
UNION ALL
SELECT N'Prior YTD', COUNT_BIG(*)
FROM #RangeEvents
WHERE RecordedAt >= @PriorStart AND RecordedAt < @PriorAsOf;

A February 29 reporting moment shifts to February 28 in a nonleap year. Document that anniversary rule before comparing totals. Equal calendar positions don't imply equal numbers of included days.

For completed-day reporting, choose tomorrow's midnight as the excluded boundary only after the selected date is complete. Otherwise, future records enter the total. Separate an as-of instant from an inclusive business date.

Swap Hard-Coded Years for a Year Parameter

Some reports need a selected historical year rather than a moving clock. Pass the year as a parameter. Derive both boundaries inside the procedure from that single input.

The following procedure uses its own permanent test table. It rejects NULL and years outside the supported bounded range. Year 9999 cannot supply a next-year boundary in the date type.

CREATE TABLE dbo.YearEvents
(
    EventId int NOT NULL PRIMARY KEY,
    RecordedAt datetime2(7) NOT NULL
);
CREATE INDEX IX_YearEvents_RecordedAt
ON dbo.YearEvents (RecordedAt);
INSERT dbo.YearEvents VALUES
(1, '2024-12-31T23:59:59.9999999'),
(2, '2025-01-01T00:00:00');
GO
CREATE PROCEDURE dbo.EventsForYear @ReportYear int
AS
BEGIN
    SET NOCOUNT ON;
    IF @ReportYear IS NULL OR @ReportYear NOT BETWEEN 1 AND 9998
        THROW 51010, 'Report year must be between 1 and 9998.', 1;
    DECLARE @StartDate date = DATEFROMPARTS(@ReportYear, 1, 1);
    DECLARE @EndDate date = DATEADD(year, 1, @StartDate);
    SELECT EventId, RecordedAt
    FROM dbo.YearEvents
    WHERE RecordedAt >= @StartDate AND RecordedAt < @EndDate
    ORDER BY RecordedAt, EventId;
END;
GO
EXEC dbo.EventsForYear @ReportYear = 2024;
EXEC dbo.EventsForYear @ReportYear = 2025;

The procedure doesn't concatenate the year into dynamic SQL. Its result shape stays fixed across inputs. A caller chooses the period without taking responsibility for timestamp precision.

Use a default year only when the calling contract defines one. Silent defaults conceal missing report inputs. An explicit validation error is easier to investigate than an unexplained period change.

Test Rows on Both Sides of Midnight

Place test rows exactly at the included start and excluded end. Also include the preceding representable timestamp. These cases check the interval rather than an arbitrary sample from its middle.

Test February dates and a run during a month transition. Test the report under the timestamp convention used in production. Time-zone conversion errors survive otherwise correct calendar arithmetic.

Use actual execution plans and STATISTICS IO for access checks. Compare the intended range with the previous filter using the same inputs. Don't announce a performance gain before measuring it.

Keep the timestamp's precision in your result comparisons. Casting to date during verification hides time boundaries. A row at midnight should belong to exactly one adjacent interval.

Make the Period Meaning Visible

Removing hard-coded years saves editing, but the report still needs a clear period definition. Show the included start and excluded end. State whether the cutoff is an instant or a completed day.

Keep that definition alongside the procedure's inputs and tests. A later report change can then alter the meaning deliberately. Stable boundaries make both automation and review easier.

Related reading on this blog: Date Boundaries With DATETRUNC and EOMONTH in SQL Server 2022 and Catching Non-SARGable Queries in Action.

A rolling period is not a changing date literal, it is a defined interval with computed boundaries.

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