Function to Get Fiscal Year, Good for Reporting

/****** Object:  UserDefinedFunction [dbo].[fnGLGetFiscYear_Toy]    Script Date: 07/18/2012 12:47:45 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnGLGetFiscYear_Toy]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnGLGetFiscYear_Toy]
GO


/****** Object:  UserDefinedFunction [dbo].[fnGLGetFiscYear_Toy]    Script Date: 07/18/2012 12:47:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE FUNCTION [dbo].[fnGLGetFiscYear_Toy]
(
	@CompanyID VARCHAR(3), 
	@TranDate DATETIME
)

  
RETURNS VARCHAR(5)

AS
BEGIN

	DECLARE @FiscYear VARCHAR(9)

	SELECT @FiscYear = FiscYear
	FROM tglFiscalPeriod WHERE
	CompanyID = @CompanyID
	AND @TranDate >= StartDate
	AND CONVERT(DATETIME,(CONVERT(VARCHAR(10), @TranDate, 101))) <= EndDate

	RETURN @FiscYear
		

	
END


GO
GRANT Execute ON [dbo].[fnGLGetFiscYear_Toy] TO ApplicationDBRole
GO

 

I had to do some summarized reporting by Fiscal Year.  Thought this might help others.

Parents Reply Children
No Data