March closes the books, but a calendar-year report keeps counting into the wrong period. Fiscal years need their own start month, quarter labels, and date boundaries.
Agree How Fiscal Years Start and Get Labeled
An April-start financial year runs through the following March. A July-start year follows the same pattern with a different offset. The calculations below handle a fixed month-based schedule.
First decide what the year label means. This example labels a period by the year in which it starts. April 2025 through March 2026 therefore carries the starting-year label 2025.
Some reports label that same period by its ending year. Neither convention should be inferred from an ambiguous column name. Use FiscalStartYear or another explicit name in stored data.
I check that labeling convention before comparing two financial reports. Matching totals under different labels create avoidable arguments. A report header shouldn't need a translation meeting.
A four-four-five calendar needs different period data. Month shifting doesn't describe week-based retail periods or extra reporting weeks. Use an approved calendar for those rules rather than extending this formula by guesswork.
The sample treats dates as local business dates. Timestamp reports still need an agreed time zone before deriving those dates. Midnight boundaries must reflect the clock used by the financial process.
Shift the Date Into Calendar Quarter Space
Move the date backward by the number of months before the fiscal start. For April, that offset is negative three. YEAR and DATEPART(quarter) then describe the shifted date's position in the financial year.
This adjustment is for classification rather than preserving an anniversary day. End-of-month changes don't alter the intended quarter or start-year label. The shifted month's position supplies those labels.
DECLARE @StartMonth int = 4;
IF @StartMonth IS NULL OR @StartMonth NOT BETWEEN 1 AND 12
THROW 51040, 'Start month must be between 1 and 12.', 1;
SELECT d.BusinessDate,
YEAR(s.ShiftedDate) AS FiscalStartYear,
DATEPART(quarter, s.ShiftedDate) AS FiscalQuarter
FROM (VALUES (CONVERT(date, '2025-03-31')),
(CONVERT(date, '2025-04-01')),
(CONVERT(date, '2025-07-01')),
(CONVERT(date, '2026-03-31'))) AS d(BusinessDate)
CROSS APPLY (VALUES (DATEADD(month, 1 - @StartMonth, d.BusinessDate)))
AS s(ShiftedDate)
ORDER BY d.BusinessDate;The boundary dates expose the period change at April's opening. July begins the second quarter under this April-start convention. Change the start month to seven to inspect a July-based classification.
Validate your supported date range before subtracting months. Dates near the minimum can underflow the date type. Business applications should define the historical range they accept rather than relying on accidental arithmetic errors.
Fiscal years become easier to review when the shift and label are visible together. Keep the offset in one parameter. Separate formulas using different start months create inconsistent report results.
Build Fiscal Years From Actual Boundaries
A shifted date supplies the starting year, but its shifted date isn't the actual period boundary. Construct that boundary with DATEFROMPARTS. Add one year for the excluded end.
The test timestamp falls inside an April-start period. The query returns the full fiscal interval and the as-of cutoff. It deliberately separates full-year reporting from year-to-date reporting.
DECLARE @StartMonth int = 4;
DECLARE @AsOf datetime2(7) = '2025-07-15T10:30:00';
IF @StartMonth IS NULL OR @StartMonth NOT BETWEEN 1 AND 12 OR @AsOf IS NULL
THROW 51041, 'A valid start month and reporting timestamp are required.', 1;
DECLARE @FiscalStartYear int = YEAR(DATEADD(month, 1 - @StartMonth, @AsOf));
IF @FiscalStartYear NOT BETWEEN 1 AND 9998
THROW 51042, 'The fiscal range exceeds supported date boundaries.', 1;
DECLARE @FiscalStart date = DATEFROMPARTS(@FiscalStartYear, @StartMonth, 1);
DECLARE @FiscalEnd date = DATEADD(year, 1, @FiscalStart);
SELECT @FiscalStartYear AS FiscalStartYear,
@FiscalStart AS IncludedStart, @FiscalEnd AS ExcludedEnd,
@AsOf AS FiscalYearToDateCutoff;The reported end is the first day of the next period. It stays excluded in timestamp filters. Avoid constructing the final second of March, because stored timestamp precision varies.
For a completed-date report, derive the excluded next-day boundary from the agreed last included date. For an instant-based report, retain @AsOf unchanged. Those cutoffs answer different questions about partial days.
Filter Financial Rows With Half-Open Ranges
Use the same start boundary with different end values for full-year and year-to-date totals. Compare the stored timestamp directly with the boundaries. Keep fiscal classification functions out of the indexed-column predicate.
CREATE TABLE #FiscalEntries
(
EntryId int NOT NULL PRIMARY KEY,
PostedAt datetime2(7) NOT NULL,
Amount decimal(12,2) NOT NULL
);
CREATE INDEX IX_FiscalEntries_PostedAt ON #FiscalEntries (PostedAt);
INSERT #FiscalEntries VALUES
(1, '2025-03-31T23:59:59.9999999', 10.00),
(2, '2025-04-01T00:00:00', 20.00),
(3, '2025-07-15T09:00:00', 30.00),
(4, '2026-04-01T00:00:00', 40.00);
DECLARE @Start date = '2025-04-01';
DECLARE @End date = DATEADD(year, 1, @Start);
DECLARE @AsOf datetime2(7) = '2025-07-15T10:30:00';
SELECT N'Full fiscal year' AS PeriodName, SUM(Amount) AS TotalAmount
FROM #FiscalEntries WHERE PostedAt >= @Start AND PostedAt < @End
UNION ALL
SELECT N'Fiscal year to date', SUM(Amount)
FROM #FiscalEntries WHERE PostedAt >= @Start AND PostedAt < @AsOf;The fixture contains synthetic amounts, not measured financial results. Run both filters and inspect which entries qualify. In my run, both totals came to 50.00, from entries 2 and 3 only. Rows at either end are more useful tests than rows safely inside the period.
SUM returns NULL when no qualifying amount exists. Use a deliberate display rule if the report needs zero. Don't confuse missing input or an incomplete feed with a known empty financial period.
Store April-Start Fiscal Years in a Calendar Table
A calendar table avoids recomputing period labels throughout every report. Give each business date one row with its approved classification. The following sample generates one April-start financial year.
The numbers come from a small cross join of decimal digits. This avoids dependencies on another application's tables. The generated sequence is bounded by the required calendar length.
CREATE TABLE #FiscalCalendar
(
CalendarDate date NOT NULL PRIMARY KEY,
FiscalStartYear int NOT NULL,
FiscalQuarter tinyint NOT NULL CHECK (FiscalQuarter BETWEEN 1 AND 4),
FiscalStart date NOT NULL,
FiscalEndExclusive date NOT NULL
);
DECLARE @Start date = '2025-04-01';
DECLARE @End date = DATEADD(year, 1, @Start);
;WITH Digits AS
(
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(n)
), Numbers AS
(
SELECT a.n + 10 * b.n + 100 * c.n AS n
FROM Digits AS a CROSS JOIN Digits AS b CROSS JOIN Digits AS c
), Dates AS
(
SELECT DATEADD(day, n, @Start) AS CalendarDate
FROM Numbers WHERE n < DATEDIFF(day, @Start, @End)
)
INSERT #FiscalCalendar
SELECT CalendarDate, YEAR(DATEADD(month, -3, CalendarDate)),
DATEPART(quarter, DATEADD(month, -3, CalendarDate)), @Start, @End
FROM Dates;
CREATE INDEX IX_FiscalCalendar_Period
ON #FiscalCalendar (FiscalStartYear, FiscalQuarter, CalendarDate);
SELECT FiscalStartYear, FiscalQuarter,
MIN(CalendarDate) AS QuarterStart,
DATEADD(day, 1, MAX(CalendarDate)) AS QuarterEndExclusive
FROM #FiscalCalendar
GROUP BY FiscalStartYear, FiscalQuarter
ORDER BY FiscalStartYear, FiscalQuarter;A permanent version belongs in the reporting database with controlled updates. Populate enough history and future dates for approved reports. Enforce one classification per date for each schedule.
If several entities use different fiscal schedules, add a schedule identifier. Their calendars cannot share one unqualified date key. Each financial row must resolve to the correct entity's schedule.
Validate Coverage Before Joining Reports
Check for missing dates across the supported horizon. An inner join silently removes facts with no calendar row. Report missing mappings before accepting fiscal totals.
I compare quarter boundaries with the finance team's approved calendar. Arithmetic correctness alone doesn't establish an accepted business schedule. A renamed year label can be as disruptive as a wrong quarter.
Keep timestamp filtering separate from calendar classification where practical. Filter the indexed timestamp range first. Then join an approved business-date field to the calendar for grouping.
Version the schedule when the financial start month changes. Historical records need their original approved classification unless a restatement is required. Applying today's offset to every old record can rewrite the meaning of prior totals.
Don't infer a fiscal quarter from the month label displayed on a chart. Use the calendar's stored quarter. Otherwise, an April-start report can show correct rows beneath incorrect calendar-quarter headings.
Reconcile the total across all four quarters with the full-year total. Use the same cutoff and source rows. A mismatch exposes a missing mapping, overlap, or inconsistent filter.
Keep calendar coverage tests in the refresh process for reporting data. Reject duplicate schedule-date rows before joining financial facts. Multiple matches multiply amounts without changing the original entry table.
Keep One Definition Across Reports
Which starting-year label will the report owner recognize? State that convention alongside the starting month. Use the same calendar for detailed rows and summary totals.
Treat fiscal years as maintained reporting definitions rather than decorations on calendar years. Review schedule changes before recalculating historical classifications. Consistent boundaries keep comparisons understandable from one report to the next.
Related reading on this blog: A Calendar Table With Holidays for Date Math and Date Boundaries With DATETRUNC and EOMONTH in SQL Server 2022.
A fiscal year is not a renamed calendar year, it is a reporting interval defined by an approved start.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
