I don't you if you still need it or want it but I went whole hog on this one and created a function that provides a whole lot of Fiscal-related information. As stated in the header of the code, it can be used for on-the-fly singleton values or in conjunction with a "Tally" Table function to generate as many Fiscal Dates as you might need or to generate a more permanent Fiscal Calendar table.

Here's the code. It looks long but don't let the looks fool you. It's progressive so it's easy to read and it's nasty fast (generates 100 years of date information by day in about a half a second). The code also has usage examples in the header.

CREATE FUNCTION dbo.FiscalDateInfo (@pSomeDT DATETIME)

/**********************************************************************************************************************

Purpose:

Given a Calendar Date, calculate various date parts for a Fiscal Year whose definition is that it starts on the first

Monday of April and each month within the Fiscal Year starts on the first Monday of that month. It also calculates

"Start" and "Next" boundaries for each "part" including Fiscal Weeks and more.

Usage:

--===== Simple "Singleton" Syntax

SELECT FiscalYear

,FiscalMonth

,WeekOfFiscalYear

,WeekOfFiscalMonth

,CurrFiscalYearStart

,NextFiscalYearStart

,CurrFiscalMonthStart

,NextFiscalMonthStart

,CurrFiscalWeekStart

,NextFiscalWeekStart

FROM dbo.FiscalDateInfo(@SomeDT)

;

--===== Return the Fiscal Calendar Information for 100 Calendar Years from the year 2000 up to 2100.

-- This method can be used to create a permanent Fiscal Calendar Table.

-- If you don't have an fnTally function, I've attached it to this post.

SELECT CalendarDate = DATEADD(dd,t.N,'2000')

,f.*

FROM dbo.fnTally(0,DATEDIFF(dd,'2000','2100')) t

CROSS APPLY dbo.FiscalDateInfo(DATEADD(dd,t.N,'2000')) f

ORDER BY t.N

;

Programmer's Notes:

1. If you make a Fiscal Calendar Table from this, I suggest using the "CalendarDate" column as the Clustered PK.

2. Consider NOT making a table from this because it's 100% memory (no READs whatsoever) and it's nasty fast.

The 100 year example (36,526 days) takes only 552ms to run and that includes piping the output to the screen.

3. Because only date functions were used, Leap Years are handled auto-magically.

Revision History:

Rev 00 - 24 Dec 2014 - Jeff Moden - Intial creation andd Unit Test.

**********************************************************************************************************************/

RETURNS TABLE AS

RETURN

WITH

cteFirstOfCurrMonth AS

( --=== Finds the first of the month for the given date

SELECT FirstOfCurrMonth = DATEADD(mm,DATEDIFF(mm,0,@pSomeDT),0)

)

,

cteFirstOfOther AS

( --=== Finds first of previous and next months

SELECT FirstOfPrevMonth = DATEADD(mm,-1,FirstOfCurrMonth)

,FirstOfCurrMonth

,FirstOfNextMonth = DATEADD(mm, 1,FirstOfCurrMonth)

FROM cteFirstOfCurrMonth

)

,

cteFiscalMonthStart AS

( --=== Finds the first Monday of the months from above

SELECT PrevFiscalMonthStart = DATEADD(dd,DATEDIFF(dd,-6,FirstOfPrevMonth)/7*7,0)

,CurrFiscalMonthStart = DATEADD(dd,DATEDIFF(dd,-6,FirstOfCurrMonth)/7*7,0)

,NextFiscalMonthStart = DATEADD(dd,DATEDIFF(dd,-6,FirstOfNextMonth)/7*7,0)

FROM cteFirstOfOther

)

,

cteOffSet AS

( --=== If the given date is less than the first Monday of the month, then offset everything by -1 month

-- Can't just subtract a month here because months don't have an even number of weeks. We have to do the "Monday" thing.

SELECT CurrFiscalMonthStart = CASE WHEN @pSomeDT < CurrFiscalMonthStart THEN PrevFiscalMonthStart ELSE CurrFiscalMonthStart END

,NextFiscalMonthStart = CASE WHEN @pSomeDT < CurrFiscalMonthStart THEN CurrFiscalMonthStart ELSE NextFiscalMonthStart END

FROM cteFiscalMonthStart

)

,

cteFiscalBasics AS

( --=== Calculate the fiscal week starts, Year, and Month

SELECT FiscalYear = YEAR(CurrFiscalMonthStart) - CASE WHEN MONTH(CurrFiscalMonthStart) <= 3 THEN 1 ELSE 0 END --Previous year if Jan, Feb, or Mar

,FiscalMonth = (MONTH(CurrFiscalMonthStart)+8)%12+1 --The +8 is the other 9 months-1 because of the 0-based modulus

,WeekOfFiscalMonth = DATEDIFF(dd,CurrFiscalMonthStart,@pSomeDT)/7+1 --Number of weeks since the first of the fiscal month +1

,CurrFiscalMonthStart

,NextFiscalMonthStart

,CurrFiscalWeekStart = DATEADD(dd,DATEDIFF(dd, 0,@pSomeDT)/7*7,0) --Figures out the Monday equal to or prior to the date

,NextFiscalWeekStart = DATEADD(dd,DATEDIFF(dd,-7,@pSomeDT)/7*7,0) --Figures out the Monday equal to or prior to the date +1 week

FROM cteOffset

)

,

cteFiscalYears AS

( --=== Calculate the start of the current and next fiscal years

SELECT FiscalYear

,FiscalMonth

,WeekOfFiscalMonth

,CurrFiscalYearStart = DATEADD(dd,DATEDIFF(dd,-6,DATEADD(mm,(FiscalYear-1900)*12+ 3,0))/7*7,0) --Adds the year to date "0" as months + 3 and finds the first Monday of the year

,NextFiscalYearStart = DATEADD(dd,DATEDIFF(dd,-6,DATEADD(mm,(FiscalYear-1900)*12+15,0))/7*7,0) --Same but adds an extra 12 months

,CurrFiscalMonthStart

,NextFiscalMonthStart

,CurrFiscalWeekStart

,NextFiscalWeekStart

FROM cteFiscalBasics

)

--==== Last but not least, calculate the week of the fiscal year

SELECT FiscalYear

,FiscalMonth

,WeekOfFiscalYear = DATEDIFF(dd,CurrFiscalYearStart,CurrFiscalWeekStart)/7+1

,WeekOfFiscalMonth

,CurrFiscalYearStart

,NextFiscalYearStart

,CurrFiscalMonthStart

,NextFiscalMonthStart

,CurrFiscalWeekStart

,NextFiscalWeekStart

FROM cteFiscalYears

;

Following the example to generate the 100 years of dates, you'll need a "Tally Table" function. Here's the function that I usually use.

CREATE FUNCTION [dbo].[fnTally]

/**********************************************************************************************************************

Purpose:

Return a column of BIGINTs from @ZeroOrOne (must be a 0 or 1) up to and including @MaxN with a max value of 1 Billion.

As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

Usage:

--===== Syntax example (Returns BIGINT)

SELECT t.N

FROM dbo.fnTally(@ZeroOrOne,@MaxN) t

;

Notes:

1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.

Refer to the following URL for how it works. http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers

2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type

will cause the sequence to start at 1.

3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.

5. If @MaxN is negative or NULL, a "TOP" error will be returned.

6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger

number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with

that many values, you should consider using a different tool. ;-)

7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending

sort is required, use code similar to the following. Performance will decrease by about 27% but it's still

very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.

If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

DECLARE @MaxN BIGINT;

SELECT @MaxN = 1000;

SELECT DescendingN = @MaxN-N+1

FROM dbo.fnTally(1,@MaxN);

8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

Revision History:

Rev 00 - Unknown - Jeff Moden

- Initial creation with error handling for @MaxN.

Rev 01 - 09 Feb 2013 - Jeff Moden

- Modified to start at 0 or 1.

Rev 02 - 16 May 2013 - Jeff Moden

- Removed error handling for @MaxN because of exceptional cases.

**********************************************************************************************************************/

(@ZeroOrOne BIT, @MaxN INT)

RETURNS TABLE AS

RETURN WITH

E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10E1 or 10 rows

, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or 1 Thousand rows

, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or 1 Billion rows

SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.

UNION ALL

SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E9 -- Values from 1 to @MaxN

;