My company is on a 4-4-5 calendar, and our week goes from Sunday to Saturday and I'm looking for advice on how to return the proper Fiscal Month from a date. Right now, I'm handling this easily in PowerShell, but we are moving systems and I need to build this out in SQL Server 2016.

Our Fiscal Year 2025 started on 12/29/24. As an example, dates from Feb 23 to Feb 28 should return March as the Fiscal Month, and 3/1 ends our first fiscal week of March. March 29 ends our last fiscal week of March (the 5th week). Therefore a date of March 30th will be an April fiscal.

Is this possible to do?

This get's me close, but some issues with it:

Declare @Date date = '2025-03-01'
Select Month(DateAdd(MM,day(@date)/22,@Date))

My company is on a 4-4-5 calendar, and our week goes from Sunday to Saturday and I'm looking for advice on how to return the proper Fiscal Month from a date. Right now, I'm handling this easily in PowerShell, but we are moving systems and I need to build this out in SQL Server 2016.

Our Fiscal Year 2025 started on 12/29/24. As an example, dates from Feb 23 to Feb 28 should return March as the Fiscal Month, and 3/1 ends our first fiscal week of March. March 29 ends our last fiscal week of March (the 5th week). Therefore a date of March 30th will be an April fiscal.

Is this possible to do?

This get's me close, but some issues with it:

Declare @Date date = '2025-03-01'
Select Month(DateAdd(MM,day(@date)/22,@Date))

• sql
• sql-server
• sql-server-2016

• 4 Why not build a fiscal calender table in powershell or whatever and put the dates until 2040 (or whenever you plan to retire) – siggemannen Commented Mar 18 at 9:52
• Sorry, made a typo. Our fiscal year for 2025 started on 12/29/2024, and our first fiscal week ENDED on 1/4/2025 – chrtak Commented Mar 18 at 10:39
• Would you precise the rule for the year start? Is it "the week whose last day is in the new year"? Or "the week whose most days are in the new year"? – Guillaume Outters Commented Mar 18 at 13:44

3 Answers 3

The approach

• Identify the Fiscal Start Date – Since your Fiscal Year 2025 starts on 12/29/2024, use this as a reference.
• Determine the Week Offset – Count the number of weeks from the fiscal start date.
• Calculate the Fiscal Month – Based on the accumulated weeks, determine which fiscal month a date falls into.

Here’s a SQL Server query to compute the Fiscal Month:

DECLARE @Date DATE = '2025-03-01'; -- Example date

DECLARE @FiscalStart DATE = '2024-12-29'; -- Start of FY 2025 (Sunday)
DECLARE @WeeksSinceStart INT;
DECLARE @FiscalMonth INT;

-- Calculate number of weeks since Fiscal Year start
SET @WeeksSinceStart = DATEDIFF(DAY, @FiscalStart, @Date) / 7; 

-- Determine the Fiscal Month (4-4-5 pattern)
SET @FiscalMonth = CASE 
    WHEN @WeeksSinceStart < 4 THEN 1  -- January (4 weeks)
    WHEN @WeeksSinceStart < 8 THEN 2  -- February (4 weeks)
    WHEN @WeeksSinceStart < 13 THEN 3 -- March (5 weeks)
    WHEN @WeeksSinceStart < 17 THEN 4 -- April (4 weeks)
    WHEN @WeeksSinceStart < 21 THEN 5 -- May (4 weeks)
    WHEN @WeeksSinceStart < 26 THEN 6 -- June (5 weeks)
    WHEN @WeeksSinceStart < 30 THEN 7 -- July (4 weeks)
    WHEN @WeeksSinceStart < 34 THEN 8 -- August (4 weeks)
    WHEN @WeeksSinceStart < 39 THEN 9 -- September (5 weeks)
    WHEN @WeeksSinceStart < 43 THEN 10 -- October (4 weeks)
    WHEN @WeeksSinceStart < 47 THEN 11 -- November (4 weeks)
    ELSE 12  -- December (5 weeks)
END;

SELECT @FiscalMonth AS FiscalMonth;

My approach to this is as follows.

1. Read about the EOMONTH() built-in function. It takes a date or date/time value and returns midnight on the last day of the month in which that date occurs (or a number of months before or after).

   It's super-useful because you can do stuff like this to summarize things by month.

   SELECT SUM(sales) total_sales,
          COUNT(*) order_count
          EOMONTH(date) month_ending
     FROM orders
    WHERE date >= EOMONTH(GETDATE(), -7)
      AND date < EOMONTH(GETDATE(), -1)
    GROUP BY EOMONTH(date)
   

   This particular example query summarizes the most recent six completed months of orders.

2. Create your own EO_XYZ_CORP_FISCAL_MONTH() stored scalar function. Make it work like EOMONTH() but returning the last days of your fiscal months rather than calendar months. For the sake of performance make it a deterministic function.

3. Test the living s*** out of it. Make sure it works with dates decades into the past and future.

4. Install it in your database schemas as part of your company's standard query repertoire.

5. Start using it in place of EOMONTH() in your queries.

You can do it with Common Table Expressions that will help you divide your task by:

• determining which fiscal year the day belongs to
• counting the number of weeks since the fiscal year start
• computing the number of months, using euclidian divide tricks to get the 4-4-5 weeks pattern

Here with a table d holding dates in a column d (with two variables at start, to configure 1. which day is your first day (configured to Sunday), and 2. how to choose the first week of the year (here "the one having most of its days in the new year")):

-- Rule telling which weeks starts the year:
-- Which day does the week begin on? Use datepart(dw) notation: 1 for Sunday, 7 for Satursday.
declare @firstDayOfWeek int = 1;
-- Let's say the fiscal week starts on sundays, and we have a Sunday, December 29th;
-- @firstWeekRule can be set to:
-- - first day in new year: first week is January 5th to 11th (because the first sunday of 2025 is on the 5th)
-- - last day in new year: first week is December 29th to January 4th (because the satursday that closes that week is in 2025)
-- - most days in new year: same as previous case, because 4 days of the week are in 2025 versus 3 days in 2024
declare @firstWeekRule varchar(31) = 'most days in new year';

with
    -- Get distinct legal years from dates, as well as years before and after
    -- (because some days around New Year's Eve will belong a fiscal year different from their legal year)
    years as
    (
        select distinct year(d) y from d
        union all select min(year(d)) - 1 from d
        union all select max(year(d)) + 1 from d
    ),
    -- Legal years.
    ly as (select y, dateadd(year, y - 1900, 0) legalstart from years),
    -- Fiscal years starts.
    fys as
    (
        select *,
            -- Choose our nearest week start from the legal year start:
            -- - forward (Jan 1st to 6th) if our week start has to be in the legal year,
            -- - backward (Dec 26th to Jan 1st) if Jan 1st has to be in your starting week
            -- - either direction (Dec 29th to Jan 4th) to adapt when first week of fiscal year requires at least 4 days over the (legal) new year
            case 
                when
                    @firstWeekRule = 'first day in new year'
                    or
                    (
                        @firstWeekRule = 'most days in new year'
                        and (7 + @firstDayOfWeek - datepart(dw, legalstart)) % 7 < 3
                    )
                then dateadd(day, (7 + @firstDayOfWeek - datepart(dw, legalstart)) % 7, legalstart)
                else dateadd(day, -(7 + datepart(dw, legalstart) - @firstDayOfWeek) % 7, legalstart)
            end fiscalstart
        from ly
    ),
    -- Fiscal year ends one day before next year's fiscal start, or by default on December 31st (1 day before January 1st).
    fy as
    (
        select *, dateadd(day, -1, coalesce(lead(fiscalstart) over (order by y), dateadd(year, 1, legalstart))) fiscalend
        from fys
    ),
    -- Now count weeks as number of days divided by 7.
    w as
    (
        select
            d,
            y,
            legalstart,
            datediff(day, fiscalstart, d) / 7 w,
            -- Month is roughly week number divided by 4.
            -- However we have to transpose our 13 4-week months to a 12 month year, thus / 13 * 12.
            -- We do this division the other way round, to benefit from euclidian divides that return integers, thus putting us in "full" months instead of inbetween.
            -- But if we stop here, we get trimesters of 5-4-4 weeks, as the * 12 / 13 will make the 5th week below the threshold of 4 (not big enough to make it to February).
            -- Thus we introduce an artificial shift of 2/3 of a month (( + 2/3) * 12) / 13), so that week 5 and 9 get postponed to a month later.
            ((datediff(day, fiscalstart, d) / 7) * 12 + 8) / 13 / 4 m
        from d join fy on d.d between fy.fiscalstart and fy.fiscalend
    )
select
    y [Fiscal year],
    m + 1 m [Fiscal month],
    format(dateadd(month, m, legalstart), 'MMMM') month [Fiscal month name],
    d [Look up day]
from w order by d;
-- Or to summarize the weeks over the full dataset, and verify we get the 4-4-5 pattern:
--select y, m + 1 m, format(dateadd(month, m, legalstart), 'MMMM') month, min(d) fromday, max(d) today, count(distinct w) n_weeks
from w group by y, m, legalstart order by y, m;

/!\ This solution works on dates, not on datetimes; if your data include datetimes, the between fy.fiscalstart and fy.fiscalend needs to be adapted not to miss rows on December 31st.

See it running for all days of 2025 in a fiddle (or specifically targeting SQL Server 2016):