Thought I'd throw my hat into the ring. As always, details are in the comments in the code.

CREATE FUNCTION dbo.FyParts

(@Date DATETIME)

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

Purpose:

Given any date after 31 Dec 1899, return various obviously named date parts as a single row table

based on a fiscal year starting on 01 July of any year.

Usage:

--===== With multiple dates from a table

SELECT t.SomeDate, ca.*

FROM dbo.SomeTable t

CROSS APPLY dbo.FyParts(t.SomeDate)

;

--===== With a single date (could be a variable and can always select an individual column value)

SELECT * FROM dbo.FyParts('JAN 15, 2015')

;

Revision History:

Rev 00 - 30 Jul 2015 - Jeff Moden - Initial creation and unit test.

- Ref: http://www.sqlservercentral.com/Forums/Topic1706648-391-1.aspx

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

RETURNS TABLE AS

RETURN WITH

cte AS (SELECT FyStart = DATEADD(mm,6,DATEADD(yy,DATEDIFF(yy,0,DATEADD(mm,-6,@Date)),0))

,MonthStart = DATEADD(mm,DATEDIFF(mm,0,@Date),0)

,MonthEnd = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,-1,@Date),0))

)

SELECT FyStart

,FyEnd = DATEADD(dd,-1,DATEADD(yy,1,FyStart))

,FyYear = DATENAME(yy,FyStart)

,FyMonth = RIGHT(DATEDIFF(mm,FyStart,@Date)+101,2)

,MonthStart

,MonthEnd

,FyDaysMonthStart = DATEDIFF(dd,FyStart,MonthStart)+1

,FyDaysMonthEnd = DATEDIFF(dd,FyStart,MonthEND) +1

,FyDaysDate = DATEDIFF(dd,FyStart,@Date) +1

FROM cte

;

You can also pass it a value like 'Jul 2015' or 'July 2015' or just about any other legal rendition of a date and it'll auto-magically work. And, yeah, if you pass it just '2015' (in quotes like that), it'll treat the data as '2015-01-01'. 'Tis the nature of the DATETIME datatype to do so. Just keep in mind that will return the values for the Jul 2014 thru Jun 2015 fiscal year because that's what the first day of 2015 is a part of.