Posted by (JavaScript must be enabled to view this email address) on Tue 28 Jul 2009

The first thing to do is get the current month:

SET @CurrentDate = GetDate()SET @CurrentMonth = Month(@CurrentDate)

Then its just a case of setting the start and end of the fiscal year according to whether the month falls in the last quarter (Jan, Feb, Mar) or the first three quarters (April thru to Dec):

IF @CurrentMonth >= 4
BEGIN	
SET @FiscalYearStart = DATEADD(mm, 3, DATEADD(yyyy, DATEDIFF(yyyy, 0, @CurrentDate), 0))	
SET @FiscalYearFinish = DATEADD(mm, -1, DATEADD(yyyy, 1, @FiscalYearStart))
END
ELSE
BEGIN	
SET @FiscalYearStart = DATEADD(mm, -9, DATEADD(yyyy, DATEDIFF(yyyy, 0, @CurrentDate), 0))	
SET @FiscalYearFinish = DATEADD(mm, -1, DATEADD(yyyy, 1, @FiscalYearStart))
END

Hope this helps you in your report building duties full code below:

Declare @CurrentDate AS DateTime
Declare @CurrentMonth AS int
Declare @FiscalYearStart AS DateTime
Declare @FiscalYearFinish AS DateTime
SET @CurrentDate = GetDate()
SET @CurrentMonth = Month(@CurrentDate)
IF @CurrentMonth >= 4
BEGIN	
SET @FiscalYearStart = DATEADD(mm, 3, DATEADD(yyyy, DATEDIFF(yyyy, 0, @CurrentDate), 0))	
SET @FiscalYearFinish = DATEADD(mm, -1, DATEADD(yyyy, 1, @FiscalYearStart))
END
ELSE
BEGIN	
SET @FiscalYearStart = DATEADD(mm, -9, DATEADD(yyyy, DATEDIFF(yyyy, 0, @CurrentDate), 0))	
SET @FiscalYearFinish = DATEADD(mm, -1, DATEADD(yyyy, 1, @FiscalYearStart))
END
SELECT @FiscalYearStart, @FiscalYearFinish